Transposing data

[Brian]

Howdy All,

I receive some prospecting data in a format where:

Column A
Odds rows contain client name: John Q. Public
Even rows contain salesman name: Assigned to Alfred E. Newman

Column B
Odds rows contain location information: Northeast
Even rows contain various data to be deleted.


What I want to have as an end results would be:

Column A
Only client name

Column B
Only salesman name

Column C
Location information

I need to:
1) Leave data in Column A odds rows
2) transpose data from Column A even rows to Column B
3) delete data in even rows in column B
4) transpose data from Column B odd rows to Column C
5) delete even rows (which will be empty after completing 1-4)

Hope I am clear on the objectives.

Thanks a million!

Brian

 

[Barb Reinhardt]

I'd probably put in a helper column (1) with the following

=mod(row(),2)

Once it's in place, I'd copy and paste special with values.

I'd put in another helper column (2) with =row() and once in place, copy and
paste special with values.

I'd sort by the helper column 1 and insert a cell to the left of the data
that you want in the "second column" for that data only.

I'd then sort by helper column 2 and delete the top empty cell in the "2nd
column".

Try that on a copy of your document and come back and let me know if it's
what you want.

 

[Barb Reinhardt]

Before doing anything I suggested before, insert a column between your
existing column A and B. Then try what I suggested before.

 

[Brian]

Thanks Barb.

I will give it a try, but probably not until Monday.

 

[Alan Beban]

If the functions in the freely downloadable file at
http:/home.pacbell.net are available to your workbook, then assuming
your data begins in Cell A1 on Sheet6, enter the following in Cells A1,
B1, C1, respectively, of Sheet 7 and fill down:

=(INDEX(ArrayAlternates(Sheet6!A$1:A$10),ROW(A1)))
=RIGHT(INDEX(ArrayAlternates(Sheet6!A$1:A$10,FALSE),ROW(A1)),LEN(INDEX(ArrayAlternates(Sheet6!A$1:A$10,FALSE),ROW(A1)))-12)
=(INDEX(ArrayAlternates(Sheet6!B$1:B$10),ROW(A1)))

Alan Beban

 

[Harlan Grove]

Alan Beban wrote...

If the functions in the freely downloadable file at
http:/home.pacbell.net are available to your workbook, then assuming
your data begins in Cell A1 on Sheet6, enter the following in Cells A1,
B1, C1, respectively, of Sheet 7 and fill down:

=(INDEX(ArrayAlternates(Sheet6!A$1:A$10),ROW(A1)))

=(INDEX(ArrayAlternates(Sheet6!B$1:B$10),ROW(A1)))

....

And these are more straightforward than

=INDEX(Sheet6!$A$1:$B$10,2*ROWS(A$1:A1)-1,1)

=INDEX(Sheet6!$A$1:$B$10,2*ROWS(A$1:A1),1)

=INDEX(Sheet6!$A$1:$B$10,2*ROWS(A$1:A1)-1,2)

how?!