Fill in Blanks based on next column

njbeancounter

Board Regular
Joined
Oct 7, 2002
Messages
158
I have a data dump which results in blank cells in column H.

The dump also brings in batch numbers in column I.

I want to fill the blanks in H based on a non-blank string based on the same batch number from column I.

ie:

Column H Column I

ach123 29874
38813 29875
BLANK 29875

So I want to fill the BLANK with 38813.

The issue is that there could be 2 or 3 BLANKs in a row.

thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Joe4...thanks

While that is handy, there are instances where the next non-blank row has a different batch number.

I have to base the "copy" on the batch number listed in the other column.
 
Upvote 0
Will the row with the matching value always be above the blank row, or could it also be below it, i.e.
Code:
[COLOR=#333333]Column H    Column I[/COLOR]

[COLOR=#333333]ach123      29874
[/COLOR][COLOR=#333333]BLANK       29875[/COLOR]
[COLOR=#333333]38813       29875[/COLOR]
Also, is there any possibility that we can sort the data before working it?
If so, we could sort by Column I first, and then Column H second.
Then we should be able to use the technique I provided the link for.
 
Last edited:
Upvote 0
Could be either...i am trying to write it to check below first, and if the batch number is not the same, to go up
 
Upvote 0
Sorry, I was in the middle of editing my previous reply when I got interrupted.
Can you look at the last question I posed?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top