transposing multiple records (sort of)

Posted by anno on November 15, 2001 8:41 PM

i have about 700 records in a spreadsheet, each arranged in two columns, consisting of text entries (column b)against 9 categories (column a). the same 9 categories appear for each record but not all categories in each record have text against them. there is one row between the end of one record and the start of the next record. my problem is that each record is arranged like this:
SITE text
NAME text
DEPT
EMAIL text
FLAGS text
ATTRIB
DESC text
AGENCY text
SUBJECT

SITE text
NAME
DEPT text
EMAIL
FLAGS text
ATTRIB
DESC text
AGENCY
SUBJECT text

...and i need all the text entries from each record arranged into columns under the existing headings (ie. SITE, NAME, etc)so i end up with the information in 9 columns and 700 rows. i know i could record a macro for 'copy|paste special|transpose' but i'd like to avoid having to do that for each record, and would prefer a non-vba solution (but i am open to any suggestions).

i hope this makes sense. i remember seeing similar questions to this before but can't find them on this page or either of the archives.
thanks in advance
anno

Posted by Ben W. on November 15, 2001 10:23 PM

If you where not opposed to a quick VBA solution you could use the following code. It assumes that the first label "SITE" is in cell A1:

Sub sort_of_transposing_multiple_records()

Dim Current_cell As range

range("A1").EntireRow.Insert
range("A2:A10").Copy
range("A1").PasteSpecial Transpose:=True
range("A2").EntireRow.Insert

Set Current_cell = range("A2")

Do While (Current_cell(2, 1).Value &LT;&GT; "")
For lcv = 1 To 9
Current_cell(1, lcv).Value = Current_cell(2, 2).Value
Current_cell(2, 1).EntireRow.Delete
Next lcv
Set Current_cell = Current_cell(2, 1)
Loop

End Sub

Otherwise I would copy the column you want to transpose 9 times with spaces between the columns. Then auto fill numbers next to it so that each column has one catagory with increasing numbers next to it.

Column 1 would have a 1 next to the first site and a 2 next to the second site and so on. Column 2 would have a 1 next to the first Name and a 2 next to the second name and so on. Then I would sort each column by the numbers next to it and throw away anything below the numbers. After you delete the columns with numbers you will be left 9 columns, each representing one catagory and 700 rows.