Excel 2007 : Excel Data Sorting Question

You don't seem to have had a response yet.

Assuming that you have headings in row 1, the zip codes are in column A, the data points are in column B, and there is no data in column C and beyond, try this in cell C2:

=IF($A2=$A1,"",IF(COLUMN(C1)-COLUMN($B1)>COUNTIF($A:$A,$A2),"",INDEX($B:$B,MATCH($A2,$A:$A,0)+COLUMN(C1)-COLUMN($B1)-1)))

Drag across and down.


You can copy and paste special | values to convert the formulae to text. You could then filter to remove duplicate rows.

The number of assumptions and the lack of a sample workbook may be the reason you have had no replies.


For an automated solution, have a look at the attached workbook.

The code is:

Please Login or Register  to view this content.

The earlier version, CollateDataPoints, does the same thing but has a fixed number of columns.

I hope this helps

Regards