Please need help with INDEX/MATCH or other formulas (see post)

[jwilson7]

I am needing to copy my Item Number value (B2) to cells B5-B7 using index/match or any other formula. I need that Item Number to be copied until a new item number is reached (20100005). Then that item number needs to be copied to cells B16-B19 and so on.

The picture is an example spreadsheet. I cannot just copy and drag because my actual spreadsheet has over 400,000 rows

Thanks

 

[kweaver]

Welcome to Mr. Excel,

You don't need to copy your entire worksheet...just sample data. If you use XL2BB, helpers here won't have to try to recreate your data.

Is a VBA solution acceptable?

 

[jwilson7]

Welcome to Mr. Excel,

You don't need to copy your entire worksheet...just sample data. If you use XL2BB, helpers here won't have to try to recreate your data.

Is a VBA solution acceptable?

My apologies. Id rather not because it is a spreadsheet that will need to be added to each year for the company I work for sorry

 

[jwilson7]

My apologies. Id rather not because it is a spreadsheet that will need to be added to each year for the company I work for sorry

Should I delete and repost with XL2BB?

 

[kweaver]

No need to delete the previous. Just for the future.

 

[jwilson7]

No need to delete the previous. Just for the future.

Will do. Do you have any ideas on my problem?

 

[kweaver]

I thought I had one or two, but think it has to be done with VBA. I'll look at it again tomorrow. maybe with a helper column it can be done. Not sure.
Don't give up hope. Lot's of good people here. Someone might have a solution.

 

[Alex Blakenburg]

I agree with @kweaver. If you don't want to use VBA or Power Query then I have used the approach below quite a bit in the past which uses helper columns.
When you get new data just copy it into the the left hand side and just make sure the formulas on the right hand side go all the way to the bottom.
If you make the whole thing a table you don't even need to extend the formula, the table will do that for you.

I find a yes / no flag helpful if you want to go straight to a pivot, where you can use it as a filter. Alternatively you can filter it in place.

20210708 Repeat Headings Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	PROJECTNUMBER	ItemNumber	Description	Date	UnitQty	Est	Avg	Award	Detail Line YN	Item No	Item Description
2		20100000	Clear and Grub		Lump/Sum				No	20100000	Clear and Grub
3									No	20100000	Clear and Grub
4									No	20100000	Clear and Grub
5	Project 1		Job 1	1/01/2011	1	10	15	12	Yes	20100000	Clear and Grub
6	Project 2		Job 2	1/01/2011	1	22	16	18	Yes	20100000	Clear and Grub
7	Project 3		Job 3	1/01/2011	1	17	19	16	Yes	20100000	Clear and Grub
8									No	20100000	Clear and Grub
9			Average for first quarter						No	20100000	Clear and Grub
10									No	20100000	Clear and Grub
11									No	20100000	Clear and Grub
12									No	20100000	Clear and Grub
13		20100005	Rem of Debris						No	20100005	Rem of Debris
14									No	20100005	Rem of Debris
15									No	20100005	Rem of Debris
16	Project 6		Job 1	1/01/2011	1	56	19	34	Yes	20100005	Rem of Debris
17	Project 7		Job 2	1/01/2011	1	36	66	46	Yes	20100005	Rem of Debris
18	Project 8		Job 3	1/01/2011	1	35	43	33	Yes	20100005	Rem of Debris
19	Project 9		Job 4	1/01/2011	1	2	2	2	Yes	20100005	Rem of Debris
20									No	20100005	Rem of Debris
21			Average for first the year						No	20100005	Rem of Debris
22									No	20100005	Rem of Debris
Data

Cell Formulas
Range	Formula
I2:I22	I2	=IF(ISBLANK(A2),"No","Yes")
J2:J22	J2	=IF(ISBLANK($B2),J1,$B2)
K2:K22	K2	=IF(ISBLANK($B2),K1,$C2)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help using INDEX/MATCH or other formul to copy cell value until next value is reached
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Also asked here Need help with INDEX/MATCH or other formulas (see post)