Re-formatting

[PaddyR]

I have a large spreadsheet (9500 lines) with data organized in the following
way:

name blank hire date starting salary
title blank blank current salary

I need to reorganize this information into a single line format which reads:

name title hire date starting salary current salary

Because of the length of the document I need to find the best, automatic way
to accomplish this change. I'm new to Excel 2007 so would be grateful for
any, simplified assistance.

 

[Argy - Arcasoft]

Hi Paddy;
? What I understood is that you have every record in two lines. In other
words, total of fields 8 but splitted in two lines. Is this also true for the
data? Eg:

name blank hire date starting salary
title blank blank current salary

Robert blank 07/14/1998 $ 50,000
Manager blank blank 75,000

Mike blank 09/03/1982 25,000
Assistant blank blank 65,000

Please confirm

Argy

 

[Argy - Arcasoft]

One more question, if what I said is true, is there any spaces in between the
data? And considering that as a true conclusion, what you really have is
(9500/2) = 4,750 records. Is this correct?

Argy

 

[PaddyR]

What you say is true. In my example, Row 1 contains cells A1 (Name) B1(empty
cell) C1 (Hire Date) & D1 (Starting Salary)

Row 2 contains cells A2 (Title) B2 (empty cell) C2 (empty cell) & D2
(Current Salary)

The second record includes the same information on Row 3 and Row 4.
The third record includes the same information on Row 5 and Row 6, etc.

In my new spreadsheet, for purposes of sorting and analysis, I need to
transfer cells A1 (described above) to A1; A2 to B1; C1 to C1; D1 to D1; and
D2 to E1.

It's also possible to perform this task by simply moving cells around in the
existing spreadsheet and adding new headings. But, with 4,750 records I need
to find a shortcut option that works across the entire list.

Thanks for your response!

PaddR

 

[Pete_UK]

Insert a new worksheet and put the formulae below in the cells stated:

A1: =INDIRECT("Sheet1!A"&2*ROW()-1)
B1: =INDIRECT("Sheet1!A"&2*ROW())
C1: =INDIRECT("Sheet1!C"&2*ROW()-1)
D1: =INDIRECT("Sheet1!D"&2*ROW()-1)
E1: =INDIRECT("Sheet1!D"&2*ROW())

These will give you the headings from Sheet1, but copy them down and
you will have the data arranged as you would like. You might like to
format column C as a date, and D and E as currency.

When you are done you can fix the values, and that will allow you to
delete the original data (make sure you have a backup copy of that in
case you might want it for something else).

Hope this helps.

Pete

 

[Argy - Arcasoft]

Hi Paddy;

this is one of those things that although there are almost infinite ways of
doing it, no matter what you choose, it will be very demanding, boring, and
particularly very frustrating. The fact that there is a lot of data
manipulation increments the error possibility exponentially. Of course, there
is always a way to take shortcuts and with the right help, this may be
resolve in a matter of minutes and with acute precision. Here is your
solution and it should take you 10 minutes. You MUST follow step by step my
instructions, so you get the result you expect. Here we go:

0- Before you start, Make your that the sheet where you are going to do
everything is completly empty and that there is nothing on it.
1- Copy all the data starting on A2 on new sheet. If your email is correct,
you have four fields, from A to D and every record is divided in two rows. I
am assuming that we don't want to carry on the blanks. So they will disappear
in the process.
2- Copy formula below in F2:
=IF(LEN(F1)>0,"",A2&"|"&TEXT(C2,"mm/dd/yyyy")&"||"&D2&"|||"&A3&"||||"&D3)
3- Copy previous formula from F2 to the last row or F(n) aprox. F9600
4- Copy formula below in H2
=IF(LEN(H1)>0,"",LEFT($F2,FIND("|",$F2,1)-1))
5- Copy formula below in I
=IF(LEN(I1)>0,"",MID($F2,FIND("|",$F2,1)+1,FIND("||",$F2,FIND("|",$F2,1))-FIND("|",$F2,1)-1))
6- Copy formula below in J
=IF(LEN(J1)>0,"",MID($F2,FIND("||",$F2,1)+2,FIND("|||",$F2,FIND("||",$F2,1))-FIND("||",$F2,1)-2))
7- Copy formula below in K
=IF(LEN(K1)>0,"",MID($F2,FIND("|||",$F2,1)+3,FIND("||||",$F2,FIND("|||",$F2,1))-FIND("|||",$F2,1)-3))
8- Copy formula below in L2
=IF(LEN(L1)>0,"",RIGHT($F2,LEN(F2)-FIND("||||",$F2,1)-3))
9- Copy H2:L2 to the last row with that or H(n) aprox H9600
Here you will see that the data has been grouped and that now it look like
records, but still has blank rows in between. Ok the next step is to clean
the file to get it in the format you need.
10- You MUST convert all formulas into values. To do this, you can take the
whole sheet and click COPY and the PASTE/ PASTE SPECIAL/VALUES. This will
convert everything in values.
11- Delete columns A to G. Column H now is Column A, and it is your first
field.
12- Name the fields in row 1:
a. A1 = Name
b. B1 = Hire Date
c. C1 = Starting Salary
d. Title
e. Current Salary
13- Do a Filter in Row 1
14- Filter out all that is blank
15- Delete the filter result; this are the in between blank rows
16- Clear the Filter and My friend, you have got your data.

Steps 13 to 16 offers several ways to do them. I am sure you have used
others before. Anything you feel confortable with, go ahead and use it for
this last part.


I would appreciate that you tell me how did it go.

Argy