How do I "flatten" this data in Excel and remove blanks?

AlsoNamedBort · September 10, 2013, 3:39pm

I have a bunch of poorly formatted data that I need to fix. Basically, it looks like this:

And so on and so forth for 50 some columns.

What I need is for it to look like this:
BILL…|22…|GRAY
GARY.|25…|GRAY
KATE.|23…|WHITE

Any way I can do this? I tried remove duplicates but I can’t really get that to work.

JerrySTL · September 10, 2013, 3:49pm

Does it say “BLANK” or are those cells empy?

AlsoNamedBort · September 10, 2013, 3:55pm

They’re empty.

JerrySTL · September 10, 2013, 4:02pm

Import the spreadsheet into an Access database. Call the table Enalzi2. Make the first field name Name, second Numbers, and third Colors. If the empty cells are null values, the following will work on the data provided.

SELECT Enalzi2.[Name], Min(Enalzi2.Numbers) AS MinOfNumbers, Min(Enalzi2.Colors) AS MinOfColors
FROM Enalzi2
GROUP BY Enalzi2.[Name];

However you mention that you have more fields and records so there could be other issues.

Then there’s the matter of having Access as it’s not in many versions of Office.

Tell you what - if you have access to Access (pun intended) AND the data isn’t sensitive, send the spreadsheet or the raw data with the | to me at:

oclv5200@hotmail.com

I’ll see what I can do with it.

Seydell · September 10, 2013, 4:04pm

nm. miss-read OP

Johnny_L.A · September 10, 2013, 4:05pm

Try this:

Sort on the third column. Delete all of the records where the third column is blank.

Sort on the second column. Delete all of the records where the second column is blank.

This will leave you with the given name in column one, the number in column two, and the surname in column 3 for all of the remaining records.

You may want to insert a new Column A and put in sequential numbers (1, 2, 3, etc. Start the sequence and then drag the lower-right corner to the end of the column) in case you need to put it into the original sort order.

Cheesesteak · September 10, 2013, 4:09pm

Is each name unique?

If so, insert new columns next to each data column and paste the names alongside, effectively creating 50 or so 2-column tables. Sort each table individually by the data field and delete all the blank rows, then re-sort by name. If each 2-column table is fully populated with names and data, the data should now line up, and you can delete the extra columns of names.

Or, if the data is not consistent, in a new sheet, list all the names, and VLookup each 2-column table for the appropriate value in each column.

AlsoNamedBort · September 10, 2013, 4:11pm

Never used Access and the data is confidential. Thanks anyway.

Unless I’m misunderstanding you, I don’t think that will work. If say, C3 is blank but B3 has a value. When I delete Row 3 I’ll lose the value in B3.

Jas09 · September 10, 2013, 4:22pm

Simlar to Johnny LA:

Sort by Col A, then Col B. This gives you a sheet like this
Bill…|22…|…|
Bill…|…|…|
Bill…|…|gray
etc

Then select Col B. Go to Find & Select->Go To Special and select blank cells. Type “=” and select (click in) the cell above the first blank (A2). Press Ctrl+Enter. This should fill all cells in column B with the correct value.

Copy the entire table and paste as values only in a new sheet (or even right on top of itself).

Then sort on Column A followed by Column C. Rinse and repeat the previous steps until all 50 columns are filled.

Then delete duplicates.

AlsoNamedBort · September 10, 2013, 4:31pm

Thanks folks. I ended up finding a macro that did pretty much what I needed.

Sign in - Google Accounts (Number 3)

PoorYorick · September 10, 2013, 4:32pm

Create a pivot table of the data. Place each column as a row. This will group the data returning only unique rows. Copy the table and paste as values.