Merged Cells Causing Problems!

[Carrie Dragicevic]

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.

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!

[#2]

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.