# How do I "flatten" this data in Excel and remove blanks? **URL:** \ **Category:** Factual Questions\ **Created:** [September 10, 2013, 3:39pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502 "2013-09-10T15:39:32Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![AlsoNamedBort](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/alsonamedbort/32/3213_2.png) [@AlsoNamedBort](https://boards.straightdope.com/u/AlsoNamedBort)\ **Post date:** [September 10, 2013, 3:39pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/1 "2013-09-10T15:39:32Z")
I have a bunch of poorly formatted data that I need to fix. Basically, it looks like this: BILL…|BLANK|GRAY BILL…|22…|BLANK BILL…|BLANK|BLANK GARY.|BLANK|BLANK GARY.|BLANK|BLANK GARY.|25…|GRAY GARY.|BLANK|BLANK KATE.|23…|BLANK KATE.|BLANK|WHITE KATE.|BLANK|BLANK 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. ---
**Author:** ![JerrySTL](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)\ **Post date:** [September 10, 2013, 3:49pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/2 "2013-09-10T15:49:46Z")
Does it say “BLANK” or are those cells empy? ---
**Author:** ![AlsoNamedBort](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/alsonamedbort/32/3213_2.png) [@AlsoNamedBort](https://boards.straightdope.com/u/AlsoNamedBort)\ **Post date:** [September 10, 2013, 3:55pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/3 "2013-09-10T15:55:27Z")
They’re empty. ---
**Author:** ![JerrySTL](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)\ **Post date:** [September 10, 2013, 4:02pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/4 "2013-09-10T16:02:06Z")
> [@AlsoNamedBort](#): > > They’re empty. 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](mailto:oclv5200@hotmail.com) I’ll see what I can do with it. ---
**Author:** ![Seydell](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@Seydell](https://boards.straightdope.com/u/Seydell)\ **Post date:** [September 10, 2013, 4:04pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/5 "2013-09-10T16:04:42Z")
nm. miss-read OP ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [September 10, 2013, 4:05pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/6 "2013-09-10T16:05:59Z")
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. ---
**Author:** ![Cheesesteak](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cheesesteak/32/3694_2.png) [@Cheesesteak](https://boards.straightdope.com/u/Cheesesteak)\ **Post date:** [September 10, 2013, 4:09pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/7 "2013-09-10T16:09:23Z")
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. ---
**Author:** ![AlsoNamedBort](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/alsonamedbort/32/3213_2.png) [@AlsoNamedBort](https://boards.straightdope.com/u/AlsoNamedBort)\ **Post date:** [September 10, 2013, 4:11pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/8 "2013-09-10T16:11:48Z")
> [@JerrySTL](#): > > 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](mailto:oclv5200@hotmail.com) > > I’ll see what I can do with it. Never used Access and the data is confidential. Thanks anyway. > [@Johnny\_L.A](#): > > 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. 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. ---
**Author:** ![Jas09](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@Jas09](https://boards.straightdope.com/u/Jas09)\ **Post date:** [September 10, 2013, 4:22pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/9 "2013-09-10T16:22:16Z")
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. ---
**Author:** ![AlsoNamedBort](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/alsonamedbort/32/3213_2.png) [@AlsoNamedBort](https://boards.straightdope.com/u/AlsoNamedBort)\ **Post date:** [September 10, 2013, 4:31pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/10 "2013-09-10T16:31:41Z")
Thanks folks. I ended up finding a macro that did pretty much what I needed. [Sign in - Google Accounts](https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/text-functions/column-sets-to-rows) (Number 3) ---
**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\ **Post date:** [September 10, 2013, 4:32pm UTC](https://boards.straightdope.com/t/how-do-i-flatten-this-data-in-excel-and-remove-blanks/668502/11 "2013-09-10T16:32:55Z")
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.