I just recieved a 210 page excel document to “format a bit”.
Someone appears to have pasted a document into the excel workbook, so that each piece of data is in a cell, but not in “the right cell”, a little like this…
… repeated ad nauseum. As you see, some of the info is in the same column, but a different row, which makes things harder. I am to put all of the data points on one line, in other words, just move the data to different cells.
The “order” of the “disorder” is the same for each group of data, if you see what I mean, so I made a little macro, grab cell, drag to empty cell, make a neat little row. When I played the macro on the next datapoint, it unfortunatly pasted over the row I had just made however. It seems the macro keeps the data as well as the “movements” perhaps?
I can do this by hand, but I will be insane by the end. Does anyone understand what it is I mean, and can anyone tell me how to do it?
Save As a comma-delimited file (csv).
Then edit that file and search and replace all “,” with “,” which should remove the empty spaces between your cells.
Open the csv file in excel and save as an excel file.
Is each record always split across two lines and only two lines?
If so, I’d take it into Access and do it there; if you have Access installed and you want to email me a sample of the data (just one page and replace the sensitive bits with fictional data), I’ll see what I can do with it - If successful, I’ll send you the Access database so you can run it yourself on the real data.
If you’re up for it, my hotmail address is TheMangeTout (you need to add the @ and the hotmail.com - I’ve left it off here to (hopefully) stop it being harvested by bots).
Mangetout you are very kind I am going to try the CSV idea, I was leaning in that direction myself, I have also grabbed a “keypress recorder” to make some macros with too, I will take you up on your offer if neither of these solutions work. Thanks all for the great and fast suggestions!
Hmm, your theories interest me, and I wish to recieve your newsletter AllShookDown.
How does one handily remove the blank rows btw? I am getting big empty spaces between my rows of pretty-data I would like to be able to remove all blank rows in a selection.
This is starting to go at a hopeful speed, you are all darlings
But my employer is a dork, the document includes random info like email addres on some people, and “Page 4” notations here and there. So I have to go very, very gently.
Anyone with a “delete all empty rows in selection” command?
I come across this type of situation from time to time and have to scratch my head for a different solution each time. Some generic ideas you can use:
Suppose you have records which span 10 columns and 3 rows each and some of the cells are blank. You want to have only the full cells in one row each. OK, here we go:
In row 1, beginning in row 11, link each cell to the cell you want to copy so you now have the first record as you want it. Suppose it is ten cells which end in col 20. You now have the info for your first record in r1c11:c20.
Copy r1c11:r3c20 to the block just below it as many times as you need (as many records as you have). You now have the information you want all in one row per record with no blank cells in between but with blank rows.
Highlight all the cells and then Paste - special - value and this puts the value and takes away the reference.
You can now delete the ten first columns without losing the info.
Now just delete the blank rows and you have the info you want. I hope I am not making it sound more complex than it is.
If some fields have data and some are blank, instead of just (in cell H1) you’ll need to use some IF statements.
=IF(A1="","",A1),&IF(B1="","",B1) etc.
translation: If A1=blank, return blank, else return A1. DON’T put a space between the quote in this case.
Excel only allows so many IFs per formula I think, and I can’t remember how many, so you may have to do it in steps. Grab the first 7 cells in one cell, the next 7 in another cell, etc.
Quick way to delete empty rows: Put some Lables in the cell above your data. Name, Address, Customer No, etc.
With your cursor somewhere in that table of data, select Data > Filter, Auto Filter. Use the little arrows on the labels to filter down to rows that are completely blank. You may need to add an extra column to your data to say =IF(the cell that should have all the data in it="",0,1). Then use the auto filter in that column to only show the rows with 0 in that column. Then select all those rows at once, right-click, delete rows.
Kinda late in the game, and probably irrelevant, but…
When I’m faced with this, I usually convert to text, and use standard unix tools like awk or perl or sometimes just vi using search and replace techniques.
But then to a hammer, every problem looks like a nail. To a non-unix person, this wouldn’t be a very good solution.
I am going to try the CSV idea, I was leaning in that direction myself, I have also grabbed a “keypress recorder” to make some macros with too, I will take you up on your offer if neither of these solutions work. Thanks all for the great and fast suggestions!