Get unique rows from multiple rows with varying values

**Sarah-Ann** · 07-02-2011, 07:45 AM

Hi everyone,

I'm working with a giant spreadsheet with over 20,000 rows.

At the moment the data for an individual person is spread over several rows like attached in the Before file.

I need something to get this data in single rows, but account for the possibility that there may be clashing data that needs to be looked at by a person, and I want to highlight this like in the After file.

Two questions:

1. Is VBA the only way to do this? I'm just learning but am fairly proficient with fomulae, so was wondering if there's a way to do this via vlookup and conditional formatting?

2. If no to question 1, what would be the best way to approach this issue, are there any tutorials or functions that would save some coding here?

Thanks in advance!
Sarah

**Marcol** · 07-02-2011, 08:50 AM

Try this, althought I suspect there is more to this than your pictures suggest.

Why not post a sample workbook rather than a picture?

With your Sheet "Before"
In F2

=IF(B2<>"",A2,"")

Drag/Fill Down

In G2

=IF(COUNTIFS($A:$A,$F2,B:B,$B2)>0,$B2,"")

Drag/Fill Down then Right to Column I

Apply filters to Columns F:I and filter out the blanks in Column F

Copy the result in Columns F:I and Paste > Paste Special > Values to K1

Conditional Formatting
Select Columns K2:N100 (more or less rows as required)
Use this formula

=AND($K1=$K2,$L1<>$L2)

Hope this helps

**Sarah-Ann** · 07-03-2011, 09:16 AM

Thank you so much for the detailed answer! This is one of the most helpful forums I've ever encountered.

I can't post the workbook as the information is confidential data related to medical history of individuals.

But now I have the principle of how to do it, thank you so much, you rock!