Return first non-blank value in duplicate data

[neulite]

I have a list of duplicate cases (column A). Both duplicates have information that the other may or may not have. I would like to use INDEX MATCH to match the case number and return the first non empty value corresponding to the attribute in the event that the other duplicate is empty. I assume I would need to use a two-way INDEX MATCH.

Original data
Case	Attribute1	Attribute2	Attribute3	Attribute4	Attribute5
1	36	(blank)	3	AO	3
1	(blank)	apple	3	AO	(blank)
2	(blank)	orange	(blank)	(blank)	85
2	61	(blank)	3	AD	(blank)
3	54	orange	(blank)	BL	40
3	(blank)	(blank)	4	(blank)	(blank)

Output I am looking for
Case	Attribute1	Attribute2	Attribute3	Attribute4	Attribute5
1	36	apple	3	AO	3
2	61	orange	3	AD	85
3	54	orange	4	BL	40

[63falcondude]

With your data in A1:F7 and your output in H1:M4 (headers in row 1), try this in I2:

=INDEX(B$2:B$7,INDEX(MATCH(1,($A$2:$A$7=$H2)*(B$2:B$7<>""),0),0))

Drag the formula through M2 and then down through row 4.

If you need help getting the Unique case numbers in column H, let us know.

[neulite]

Thank you, very helpful. Is it possible (with formula) to carry over the cell color as well? Or would I need VBA for this? I don't know VBA.

[63falcondude]

Not unless the colored cells are filled through Conditional Formatting or there is some underlying logic as to why the cells are colored how they are.

If there is some sort of logic behind what should be colored, you may be able to build a Conditional Formatting formula to fill the output cells.

[neulite]

That' what I thought, thanks. Last question, is there VBA code that can do both of these, merge the duplicate data ignoring using first non-blank cells, and carrying over the format of the cell already in place?

[63falcondude]

Probably, but I don't know VBA.