Append Alternate Rrow To Previous Row
Append Alternate Rrow To Previous Row
(OP)
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?
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?





RE: Append Alternate Rrow To Previous Row
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Append Alternate Rrow To Previous Row
=INDIRECT("A"&TEXT(2*ROW(A1)-1,"##"))&", "&INDIRECT("A"&TEXT(2*ROW(A1),"##"))
RE: Append Alternate Rrow To Previous Row
Given such difficulties, why can't you get the data source to do the correct formatting in the first place?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Append Alternate Rrow To Previous Row
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.
RE: Append Alternate Rrow To Previous Row
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.
RE: Append Alternate Rrow To Previous Row
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Append Alternate Rrow To Previous Row