Home

Merged Cells Causing Problems!

Carrie Dragicevic
Occasional Visitor

I'm working a project with a large data set (>500,000 rows, 5 columns). The columns are the date, order number, invoice number, Amount A, and Amount B. There should only be ONE line for each invoice with both an Amount A and B. However, in some cases, the raw data has Amount A on one line and Amount B on the next line. The invoice column is merged.

Example1.PNG

There are also cases where multiple order numbers are on one invoice, so the amounts are broken out by order number when the goal is to have them summed for the invoice total. Again, the invoice cells are merged. 

 

If I try to unmerge all of the cells, Excel crashes. Ideally, I'd like to unmerge the cells, but have the cell data copied to all new cells (so in the above example, I would unmerge the cell and 0041043 would appear in both new cells instead of just the top one). To my knowledge, that's not possible, but I'm hoping I'm wrong. 

 

Any suggestions on how to handle this data?

 

Thanks!

1 Reply

you can create a third helper columns and use the IF function.

 

try this on a copy of the workbook.  

 

lets sau your invoice number is in Column A . 

Select the range of the merged cells which is the column A
Unmerge the cells
Home -> Find and Select -> Go to special... -> Blanks -> ok
Type "=" move one cell up and press Ctrl + Enter

 

this will enter Fill with Duplicate invoice numbers in Column A then

use a helper column, lets say your value 63.27 and 0.77 are in column B and C

then in helper column D put this formula =IF(B2<>"",B2,IF(C2<>"",C2,"")) and copy down.

 

see if this works for you.

 

Related Conversations
Merge two cells one below the other
Sam789 in Excel on
10 Replies
Hide a column with merged cell across the column
suionhk in Excel on
7 Replies
Excel 2010 Mail Merge issues
burks96 in Excel on
1 Replies
Publisher 2016 Mail Merge Not Working
jay33 in Office 365 on
0 Replies
Excel Table
Reza Dashti in Excel on
1 Replies