Seperating Data

[mesolomo]

Hi,

This is my first posting, but I'm hoping you all can help:

I am copying data from a PDF. The data I need is in columns, spread over hundreds of sheets. I would like the data in the same layout (columns), but need it in excel instead of the PDF sheets.

I've tried copying and pasting into Excel, but the problem is that what is in columns on the PDF ends up as multiple rows beneath eachother, all in column A, on excel.

I've tried Text to columns, but that seems to only work if all the data you need to seperate is in the same row. What I need to ultimately appear in one row is currently displaying in three rows, one beneath the other. Is there a way for excel to automatically move what's in cell A2, for example, to B1 (and do this for all data, all the way down the sheet, that's similar to that currently in A2?

(If it would be useful for me to post a screenshot, could someone reply with how to do that in the most version of Excel?) Thanks!!!

 

[wsjackman]

have you tried transpose on your excel data.

otherwise
in b1 enter =a2
in c1 enter =a3
select b1:c3
copy
select b1 to c in the last data point (the number of rows selected needs to be a multiple of three)
paste
copy the current selection
paste special values
select column C
use filters to select blanks
delete rows

 

[mesolomo]

Thank you wsjackman!

I was able to copy the data from the two lower rows into the next two columns automatically, which is huge.

I still haven't been able to delete the two lower rows though. How do I use the filter function to delete the blanks in column C?

Thanks for your help

 

[wsjackman]

in the filter options at the bottom of the list, there should be "blanks"

if you have more than some number of different choices, however, the list box may not show the bottom of the list.

in this case try in d1
=count(A1:C1)
copy and paste down to the bottom of the list
use the filter on column D and select 1 in the choice box
select all the ones and delete rows

note be sure you have pasted special values first

 

[mesolomo]

When I use the filter to remove blanks, they disappear from my screen, but they still reappear sometimes and the numbers of the rows are still off. Once I've filtered out blanks, how do I permanently delete the rows with the blanks?

 

[wsjackman]

I am not sure what you mean by "the numbers of the rows are still off"

did you select all the "rows" with blanks in Column d
before you selected delete rows

could you give an example of the errors you are finding?

 

[mesolomo]

I am doing something slightly different now then I was before.

I went through column A to delete some data that was in it that i didn't need, so that I could transpose rows A2, A3 to B1, C1, etc. like you explained yesterday.

When I delete the data in those rows, i have blanks that seem like they would confuse excel when I try to transpose the data, so i tried to filter them.

When I filter the blanks, though, the numbers of the rows (on the left side of the spreadsheet) count like the rows with the blanks are still there, even though I cannot see them. If, for example I deleted rows 4, 5, and 6, the numbers on the side go: 1,2,3,7,8 etc. It seems that if I try to transpose the data now, it will not work.

Additionally, when I was filtering blanks yesterday, the data that i filtered still would show up in the "text to column" example window.

It seems like even though it's filtered, it's still there and changing the layout, so is there a way to just completely remove what I've filtered?

 

[mesolomo]

Alright! I figured out the answer to that last question. I was filtering OUT the blanks, when what I needed to do was filter so I had ONLY them, then delete them, then remove the filter. I did not understand that.

One problem I'm having though is sometimes after I transpose the data, once I make any changes to any other rows, all the data in the two new columns (B and C) change to "#REF!". Why is that? Am I doing something wrong transposing the data?

I think it may have something to do with Pasting Special Values. I found the button for that, but I'm not sure I understand what it does or exactly how to do it. Could you explain that a bit more to me?

Thanks so much for your help!!!!

 

[wsjackman]

some how the rows are not being deleted.


when you select the rows are you selecting them by moving the cursor on the number indicators next to column A"?
if you select just cells, are you selecting delete rows or delete to do the deletion?

I typically click on the row number next to Column A and the row shows as selected
I then go below the filter area and while holding the shift key down click on a row number next to column A
it should show a large block of rows selected.

i have 2007 so I right click in the selected area and select delete row

in 2003
edit-delete row

 

[mesolomo]

I have 2007 too, so it ought to be the same.

I think I figured out the deleting problem- I was only filtering them out, not deleting them.

Do you understand why I'm getting the #REF! when I try to transpose data?