NEED!! help reorganizing data...

[Dan B]

Hi,
I have a long spreadsheet with many records that each one looks something
like this:

A B C D E F G
856
New York

DCOMP21
Double 816.30

I need it to look like this:

A B C D E F G F
856 New York DCOMP21 Double 816.30

Basically, I need everything in different colums on one row. Can it be done
and what is the best way?

Thanks!!!

Dan

 

[Guest]

Dan
This can be done without too much trouble. I can't really tell how your table is laid out from your sample. Give me some more detail and I can put together a procedure

1) Is every record the same number of rows? and how many
2) What cell is 856 in? (assuming this is the first record
3) What cell is New York in
4) What cell is DCOMP21 in
5) What cell is Double in
6) What cell is 816.30 in
7) Is there a blank row between each record
8) What is the first cell of the second record

Basically, you can set up formulas to pull each part of the record into a single row off to the right of the original table. Then you will copy this down along each record, the easy way to do this is to select the first set of formulas AND all of the blank cells before the begining of the second record, then grab and pull the fill handle to the bottom of the data. This will put the formulas in every "n"th row, instead of every row. Then Copy and Paste_Special>Values. Finally, delete the original data and sort to get rid of the spaces

If you provide the above info I can put together a detailed procedure for you

Good Luck
Mark Graesse
(e-mail address removed)



----- Dan B wrote: ----

Hi
I have a long spreadsheet with many records that each one looks somethin
like this

A B C D E F
85
New Yor

DCOMP2
Double 816.3

I need it to look like this

A B C D E F G
856 New York DCOMP21 Double 816.3

Basically, I need everything in different colums on one row. Can it be don
and what is the best way

Thanks!!

Da

 

[Dan B]

Mark,
Here are answers to your questions:
1)Each record starts with the 856 part from my example. It is every 5th
row, or in other words there are 4 rows in between each numbered record.
There are roughly 2100 records.

2)856 is in cell A4. It is the first part of each record. The next record
would start at A9, the next at A14 and so on.

3)New York is in A5, then the next in A10 and so on

4)DCOMP21 is in A7, then A12, A17.......

5)Double is in D8, then D13, D18.....Also, columns E thru K have data that
is all on the same row which is fine. That is how I need it....so in one
record D8 thru K8 is all OK.

6)816.30 is in H8.....part of what I mentioned in #5 above
7)No
8)A9 is the 1st cell of the second record.

So...I need everything in different columns, but on the same row. I could
email you a sample spreadsheet if that would help clarify.

THANK YOU for your help,

Dan

Dan,
This can be done without too much trouble. I can't really tell how your

table is laid out from your sample. Give me some more detail and I can put
together a procedure.

1) Is every record the same number of rows? and how many?
2) What cell is 856 in? (assuming this is the first record)
3) What cell is New York in?
4) What cell is DCOMP21 in?
5) What cell is Double in?
6) What cell is 816.30 in?
7) Is there a blank row between each record?
8) What is the first cell of the second record?

Basically, you can set up formulas to pull each part of the record into a

single row off to the right of the original table. Then you will copy this
down along each record, the easy way to do this is to select the first set
of formulas AND all of the blank cells before the begining of the second
record, then grab and pull the fill handle to the bottom of the data. This
will put the formulas in every "n"th row, instead of every row. Then Copy
and Paste_Special>Values. Finally, delete the original data and sort to get
rid of the spaces.

 

[Guest]

Hi Dan

This procedure is a little different then the one I previously described. Since so much of your data is already in seperate columns I think this will be quicker

1) Insert a new column between A and

2) In cell B8 enter this formula: =IF(E8<>"",A4,""

3) In cell C8 enter this formula: =IF(E8<>"",A5,""

4) In cell D8 enter this formula: =IF(E8<>"",A7,""

5) Select cells B8 through D8, and drag the fill handle down to the bottom of your table. The fill handle is the black square in the lower right corner of the selected cells

6) Select columns B through D, Edit>Copy, Edit>Paste_Special>Values. This will replace the formulas with the actual values

7) Delete column

8) Sort on appropriate column to remove all of the empty rows. If you sort on column A, B, or C, the pseudo-blanks ("") will end up at the top of the file so you will need to select those rows and delete them

If you have any problems you can e-mail me the file

Regards
Mark Graesse
(e-mail address removed)
----- Dan B wrote: ----

Mark
Here are answers to your questions
1)Each record starts with the 856 part from my example. It is every 5t
row, or in other words there are 4 rows in between each numbered record
There are roughly 2100 records

2)856 is in cell A4. It is the first part of each record. The next recor
would start at A9, the next at A14 and so on

3)New York is in A5, then the next in A10 and so o

4)DCOMP21 is in A7, then A12, A17......

5)Double is in D8, then D13, D18.....Also, columns E thru K have data tha
is all on the same row which is fine. That is how I need it....so in on
record D8 thru K8 is all OK

6)816.30 is in H8.....part of what I mentioned in #5 abov
7)N
8)A9 is the 1st cell of the second record

So...I need everything in different columns, but on the same row. I coul
email you a sample spreadsheet if that would help clarify

THANK YOU for your help

Da

Dan
This can be done without too much trouble. I can't really tell how you

table is laid out from your sample. Give me some more detail and I can pu
together a procedure

2) What cell is 856 in? (assuming this is the first record
3) What cell is New York in
4) What cell is DCOMP21 in
5) What cell is Double in
6) What cell is 816.30 in
7) Is there a blank row between each record
8) What is the first cell of the second record

single row off to the right of the original table. Then you will copy thi
down along each record, the easy way to do this is to select the first se
of formulas AND all of the blank cells before the begining of the secon
record, then grab and pull the fill handle to the bottom of the data. Thi
will put the formulas in every "n"th row, instead of every row. Then Cop
and Paste_Special>Values. Finally, delete the original data and sort to ge
rid of the spaces

 

[Dan B]

That worked great!
So, what does <> refer to? Does that mean anything......like if E8 =
anything then copy A4? And what does the "" do?

Thanks for the info.

Dan

Hi Dan,

This procedure is a little different then the one I previously described.

Since so much of your data is already in seperate columns I think this will
be quicker.

1) Insert a new column between A and B

2) In cell B8 enter this formula: =IF(E8<>"",A4,"")

3) In cell C8 enter this formula: =IF(E8<>"",A5,"")

4) In cell D8 enter this formula: =IF(E8<>"",A7,"")

5) Select cells B8 through D8, and drag the fill handle down to the

bottom of your table. The fill handle is the black square in the lower
right corner of the selected cells.

6) Select columns B through D, Edit>Copy, Edit>Paste_Special>Values.

This will replace the formulas with the actual values.

7) Delete column A

8) Sort on appropriate column to remove all of the empty rows. If you

sort on column A, B, or C, the pseudo-blanks ("") will end up at the top of
the file so you will need to select those rows and delete them.

 

[Guest]

Hi Dan
Glad to hear everything worked out. For your questions

The <> is used as an inequality, and the "" means blank. The IF basically says if E8 isn't blank then return A4, if E8 is blank then leave this cell blank. If you just used =A4, then you would get a bunch of extra info when you copy the formulas down. The only thing that gets messy is that "" isn't really blank. Even though the cell has nothing in it after the Paste_Special, excel still treats it as if it does have something in it. That's why the pseudo-blanks sort to the top instead of to the bottom

Regards
Mark Graesse
(e-mail address removed)

----- Dan B wrote: ----

That worked great
So, what does <> refer to? Does that mean anything......like if E8
anything then copy A4? And what does the "" do

Thanks for the info

Da

Hi Dan

Since so much of your data is already in seperate columns I think this wil
be quickerbottom of your table. The fill handle is the black square in the lowe
right corner of the selected cellssort on column A, B, or C, the pseudo-blanks ("") will end up at the top o
the file so you will need to select those rows and delete them

If you have any problems you can e-mail me the file
Regards

Mark Graesse
(e-mail address removed)
----- Dan B wrote: ----

Mark

Here are answers to your questions
1)Each record starts with the 856 part from my example. It is ever 5t
row, or in other words there are 4 rows in between each numbere record
There are roughly 2100 records

2)856 is in cell A4. It is the first part of each record. The nex

recor
would start at A9, the next at A14 and so on

3)New York is in A5, then the next in A10 and so o
4)DCOMP21 is in A7, then A12, A17......
5)Double is in D8, then D13, D18.....Also, columns E thru K have dat

tha
is all on the same row which is fine. That is how I need it....so i on
record D8 thru K8 is all OK

6)816.30 is in H8.....part of what I mentioned in #5 abov

7)N
8)A9 is the 1st cell of the second record

So...I need everything in different columns, but on the same row.

coul
email you a sample spreadsheet if that would help clarify

THANK YOU for your help
Da