Append Alternate Rrow To Previous Row 1

[Stoker]

I am importing data into an Excel table and need to rearrange the data. The problem is that the information for one person is brought into Excel on two separate rows, like this:
Last Name, First Name, ID Number
Blood Type, Height, Weight

I want to reorganize this data so that all the info for each person is on one row
Last Name, First Name, ID Number, Blood Type, Height, Weight

Then I can do the necessary charts and filter the data as required.

This process will be done regularly. The other complication is that several people will need to do this and some of these people are not very savvy with Excel. Therefore, I need to find a simple method of reorganizing this data that can be carried out by people with very little Excel knowledge. There are many records being imported and it can't be done manually. We have no control over the formatting of the data from the source.

Here is my current process:
use conditional formatting, with the criteria "=MOD(ROW(),2)=0", to change the fill color of every other row (every Blood Type, Height, Weight row gets blue fill)
use autofilter to filter out the colored rows, leaving only the Last Name, First Name, ID Number rows visible
copy and paste the Last Name, First Name, ID Number rows into a new worksheet
use autofilter on the original list to filter out the rows with no fill
copy and paste the Blood Type, Height, Weight rows into the new worksheet, to the right of the previously pasted data

Is there a better way to do this?

 

[IRstuff]

Have you tried to record this as a macro? Then, you just need to wrap the macro with a While loop.

TTFN

FAQ731-376

 

[jghrist]

If your data starts in A1, put this in another column and copy down to half the number of rows in the data.

=INDIRECT("A"&TEXT(2*ROW(A1)-1,"##"))&", "&INDIRECT("A"&TEXT(2*ROW(A1),"##"))

 

[IRstuff]

There are also parsers that can be run on the raw, assumed to be text, data files directly. If you're dealing with just poorly formatted CSV data, i.e., the data simply has a carriage return inserted after the comma at the end of the first data line, then a simple text substitution could reformat the data into a single CSV line.

Given such difficulties, why can't you get the data source to do the correct formatting in the first place?

TTFN

FAQ731-376

 

[Stoker]

Thanks for the replies. The macro solution is not an option in this case because the code would have to "live" somewhere. It can't go in the xlstart folder because the people who will be required to do this data processing do not work on dedicated machines - they move around amongst shared workstations. This solution would require maintainance as machines are added and reformatted and it also brings along the issue of security settings.

I did investigate modifying the source data outside of Excel. The challenge is that it is not a carriage return that is causing the split of each record, so I can't just do a find and replace on carriage returns. It would have to be a script that takes every second row and appends it to the previous row.

 

[yakpol]

I would do the following:

1. In the columns 4, 5, 6 of the first row of data add formulas:
cell [D1] =A2
cell [E1] =A3
cell [F1] =A4

2. Copy formulas to every 2nd row below
3. Convert all formulas to values (copy/paste special/values)
4. Sort table by the rightmost column
5. Delete data with only three columns per row
6. We're done.

 

[IRstuff]

Can you post an example of the data file?

TTFN

FAQ731-376

 

[Stoker]

Thanks Yakpol, this is what I was looking for. The challenge here was coming up with a process that can be carried out by people who do not use Excel regularly. I think this solution has a better chance of being remembered than using the conditional formatting approach and may be easier for people to follow.