×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Append Alternate Rrow To Previous Row

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?

RE: Append Alternate Rrow To Previous Row

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),"##"))

 

RE: Append Alternate Rrow To Previous Row

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: Eng-Tips.com Forum Policies

RE: Append Alternate Rrow To Previous Row

(OP)
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.
   

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources