Help with transposing data

See next code

Please Login or Register  to view this content.

For a formula option...

In E4 put this formula:

Formula:

Please Login or Register  to view this content.

In E5 put this formula and fill down a few hundred rows. This formula returns the row number each time it finds the text "NAME" in column A. (You can, and should, change 9999 to a row that is beyond your last used row in column A.)

Formula:

Please Login or Register  to view this content.

In F4 put this formula, then fill right to M4 and down as many rows as you need (rows that show values in column E):

Formula:

Please Login or Register  to view this content.

You can then format the range of transposed data to hide the 0's where no data exists. (Conditional Formatting, for cells that equal zero, set the Number Format to Custom and use ;;;)

Note: There are some instances of incomplete data sets (missing fields) in column A (e.g. rows 453-462, 735-742, 745-752, 777-783, 786-792, etc.) This could affect results, as these formulas find the NAME field/value, then get the next field values (TITLE, COUNTY) from the ensuing rows. So if there's a gap or missing field it could miss it. (The code provided by PCI also has similar limitations, including another one where two sets of data don't have any row gap between them. The code uses Areas, and when two data sets are together it sees that as one Area.)