Combining rows if 2 values are met

[losc]

Hi there, I'm trying to combine multiple rows of answers by the same subject based on an Subject number and a certain text. For example rows 2 through 4 should be combined into a single row as their subject number and text matches, this should result in Subject 123 with the text Hello this is a test, in 2010 with times picked A = 6, B = 2, C= 3 and D = 1 in this row.

I tried doing this by creating IF formulas, but I could not figure out how to have it automatically loop to keep comparing to the row below. I also tried to write a VBA code, which works for a single row and a single column, but I cannot figure out how to make it check multiple rows and loop automatically.

Any help is greatly appreciated!

[Richard Buttrey]

Hi,

I can't help thinking a Pivot Table isn't the most obvious

See attached

[MatrixMan]

Try this:

Please Login or Register  to view this content.

[losc]

Try this:

Please Login or Register  to view this content.

Thank you this seems to be working perfectly! In my actual database I use columns A-AB. After expanding from CelD onwards instead of using CelY for year and CelT for the text and CelS can I just use AC, AD, AE?

[MatrixMan]

Thank you this seems to be working perfectly! In my actual database I use columns A-AB. After expanding from CelD onwards instead of using CelY for year and CelT for the text and CelS can I just use AC, AD, AE?

Sorry - I'm not picturing this .. it's fairly easy to expand and if you've managed to get it sorted then great; if not, attach the real file and I'll adjust it for you.