Formula to pull numbers from dataset and assign them to rows

[photozs]

I have huge data set 500,000 rows. Column B contains transaction number (625, 255 etc) and company number (100,200 and 500). C=posting date (C1,C2 etc) or account (C3 etc), D=Amount (summed up in D3 etc), and E=service date (has repeating empty cell).
I need formula to assign company for each row number. The company (red text) is at the bottom of each group (100,200 and 500). For example, in A1 and A2 formula needs to pull 100. In A5 and A6, I need 200 etc.

Currently I filter column E to blanks, select visible cells and copy to column A, then manually copy companies (for example, I copy B3 to A3, than manually A3 to A1 and A2. It takes forever.

I was trying to play with lookup, row, index, if, to assign row number to each company, but nothing worked. I need something that can say search Column E and find a blank space in E3, if Blank pick B3, then move to the next and find blank space in E6 - if blank pull cell b6 etc.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Formula here needed to pull 100
[/TD]
[TD]625
[/TD]
[TD]9/13/2017
[/TD]
[TD]20
[/TD]
[TD]4/8/2017
[/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 100
[/TD]
[TD]255
[/TD]
[TD]5/9/2017
[/TD]
[TD]10
[/TD]
[TD]5/5/2017
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100
[/TD]
[TD]5900000000
[/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 200
[/TD]
[TD]256
[/TD]
[TD]8/8/2017
[/TD]
[TD]4
[/TD]
[TD]6/5/2017
[/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 200
[/TD]
[TD]656
[/TD]
[TD]9/9/2017
[/TD]
[TD]3
[/TD]
[TD]3/3/2017
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]200
[/TD]
[TD]5100000000
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 500
[/TD]
[TD]325
[/TD]
[TD]8/8/2017
[/TD]
[TD]44
[/TD]
[TD]5/5/2017
[/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 500
[/TD]
[TD]226
[/TD]
[TD]4/19/2017
[/TD]
[TD]7
[/TD]
[TD]3/3/2017
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]500
[/TD]
[TD]5200000000
[/TD]
[TD]51
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

Re: Need formula to pull numbers from dataset and assign them to rows

photozs, Welcome.

Maybe try the following ARRAY formula...

=IF(ISNUMBER(E1),INDEX(B1:B600000,MATCH(FALSE,ISNUMBER(E1:E600000),0)),"")

Confirm using Ctrl + Shift + Enter

Hope that helps.

 

[photozs]

Re: Need formula to pull numbers from dataset and assign them to rows

Thanks Snakehips!!! The formula works like magic. I am wondering if we could modify this formula to pull company number from the above?

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]100
[/TD]
[TD]5900000000
[/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 100 from B1
[/TD]
[TD]256
[/TD]
[TD]8/8/2017
[/TD]
[TD]4
[/TD]
[TD]6/5/2017
[/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 100 from B1
[/TD]
[TD]656
[/TD]
[TD]9/9/2017
[/TD]
[TD]3
[/TD]
[TD]3/3/2017
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]200
[/TD]
[TD]5100000000
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 200 from B4
[/TD]
[TD]325
[/TD]
[TD]8/8/2017
[/TD]
[TD]44
[/TD]
[TD]5/5/2017
[/TD]
[/TR]
[TR]
[TD]Formula here needed to pull 200 from B4
[/TD]
[TD]226
[/TD]
[TD]4/19/2017
[/TD]
[TD]7
[/TD]
[TD]3/3/2017
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

Re: Need formula to pull numbers from dataset and assign them to rows

Try formula in A2 and drag down....

Excel 2010
	A	B	C	D	E
1		100	5900000000	30
2	100	256	08/08/2017	4	06/05/2017
3	100	656	09/09/2017	3	03/03/2017
4		200	5100000000	7
5	200	325	08/08/2017	44	05/05/2017
6	200	226	4/19/2017	7	03/03/2017
Sheet3

Cell Formulas
Range	Formula
A2	=IF(E2="","",IF(E1="",B1,A1))

Hope that helps.

 

[photozs]

Re: Need formula to pull numbers from dataset and assign them to rows

It works. Amazing. Thanks a lot!!!!