convert vertical text groups to horizontal columns

Topic

New Reply

I have a large amount of test data consisting of a date, test name, result, and reference range. This data is laid out in a single column. So I have a Date and under that a Test Name, and under that a Result, and under that a Reference Range and then it repeats. I want to move this data into a database so I need to convert the single column to 4 columns. Any idea how to do that?

Here is what I have done so far: Below is a sample of the data. Since this is a lab work there may be a dozen tests relating to one date and time.
Date/Time: 03/01/2016 @ 0900
Test Name: GLUCOSE
Result: 336 High
Reference Range: (70-110)

Since Access shuffles data around, I began by using Excell to add a column of ID# to maintain the original order of Data. I added a second order ID to keep the date, test, result, and range related. Then a third ID to order the multiple tests under a given date/time.

My real problem is maintaining relationships in the data. I need the logic on how to do that.

Reply | Quote

Replies

One way would be to place this formula in the B1 cell and fill across 4 columns (because you have blocks of 4).
Then, fill down 1/4 of the total number of rows. If you have 16 blocks of 4, fill down 4 rows. If you have 20 blocks of 4, fill down 5 rows.

=INDIRECT(“$A”&((ROW()-1)*4+COLUMN()-1))

When finished, you might want/need to select the newly transposed data, copy it and do a paste special / values to just get the result rather than the formulas.

Reply | Quote

Actually your formula works in Excel 97 but it does not grab the first 4 entries.

Reply | Quote

I failed to mention I only have Office 97. I’m old!

Reply | Quote

Works perfectly!! Thank you KWeaver!

Reply | Quote

Seems to work in mine. See the attachment here.

Reply | Quote

I think the earlier version had INDIRECT, but am not sure…don’t run that any longer. Did you try this formula?

Reply | Quote