Multiple Row to columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this seems so simple...maybe I'm now frustrated due to looking at the
computer screen all day. I am formulating an excel sheet as my data base to
create mail merge. The website that I have gotten the information from pastes
to the excel sheet as such:

File Numer 1233112 John Doe
12563 E Washington St
Mesa, AZ 85296

I need to date to read as following

File Number 1233112 John Doe 12563 E Washington St
Mesa, AZ 85296


Paste special does not work for this function and I have over 7,000 records
to sort through.... Please Help
 
It looks like A1 contains the file number, and B1:B3 contains the
corresponding address. If so, assuming that the format is consistent
where each record is contained in three rows, and that A1:B100 contains
the data, try the following...

C1, copied down and across to Column E:

=INDEX($B$1:$B$100,(ROWS($D$1:D1)*3-3+1)+(COLUMNS($C$1:C1)-1))

If there's a blank row between each record, replace...

*3-3

with

*4-4

Then, convert the formulas to values...

1) Select/highlight Column C through Column E

2) Edit > Copy

3) Edit > Paste Special > Values > Ok

Now you can delete Column B.

Hope this helps!
 
Back
Top