Based on the ID and Header find the first non blank cell with the data and match it in the next table

[SylwekS]

Hello all,

I am facing the problem when creating the report, I was trying to use index match, lookup, vlookup, if's, connect them somehow together but never get the solution, so i hope someone will be able to help with this.

Based on the ID and Header I need to find the first non blank cell and the data which is there, this is small example i have around 60 + headers and around 100000 rows.

I will appreciate any help.

A

B

C

D

E

F

G

H

I

J

K

L

1​

What i have

Want to have

2​

3​

ID

aaa

bbb

cccc

ddd

ID

aaa

bbb

ccc

ddd

4​

1​

text1

1​

text1

text2

5​

1​

text2

2​

text3

text4

text5

6​

2​

text3

3​

text6

text7

text8

7​

2​

text4

8​

2​

text5

9​

3​

text6

10​

3​

text7

11​

3​

text8

 

[Toadstool]

Hi SylwekS,

Does this work for you?

SylwekS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	What i have						Want to have
2
3	ID	aaa	bbb	cccc	ddd			ID	aaa	bbb	ccc	ddd
4	1		text1					1		text1	text2
5	1			text2				2	text3	text4		text5
6	2	text3						3		text6	text7	text8
7	2		text4					0
8	2				text5
9	3		text6
10	3			text7
11	3				text8
12
Sheet1

Cell Formulas
Range	Formula
H4:H12	H4	=IFERROR(INDEX($A$4:$A$100000,AGGREGATE(15,6,ROW($A$4:$A$100000)-ROW($A$3)/(ISNA(MATCH($A$4:$A$100000,$H$3:H3,0))),1)),"")
I4:L12	I4	=IFERROR(INDEX(B$4:B$100000,AGGREGATE(15,6,ROW($A$4:$A$100000)-ROW($A$3)/((B$4:B$100000<>"")*($A$4:$A$100000=$H4)),1)),"")

 

[SylwekS]

Hi SylwekS,

Does this work for you?

SylwekS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	What i have						Want to have
2
3	ID	aaa	bbb	cccc	ddd			ID	aaa	bbb	ccc	ddd
4	1		text1					1		text1	text2
5	1			text2				2	text3	text4		text5
6	2	text3						3		text6	text7	text8
7	2		text4					0
8	2				text5
9	3		text6
10	3			text7
11	3				text8
12
Sheet1

Cell Formulas
Range	Formula
H4:H12	H4	=IFERROR(INDEX($A$4:$A$100000,AGGREGATE(15,6,ROW($A$4:$A$100000)-ROW($A$3)/(ISNA(MATCH($A$4:$A$100000,$H$3:H3,0))),1)),"")
I4:L12	I4	=IFERROR(INDEX(B$4:B$100000,AGGREGATE(15,6,ROW($A$4:$A$100000)-ROW($A$3)/((B$4:B$100000<>"")*($A$4:$A$100000=$H4)),1)),"")

Hello,

Thank you, I am testing it now on the production but it seems it is working perfectly, a bit heavy but this is solving my problem, saving like 80% of time and reducing risk of error

BR,
SylwekS

 

[Toadstool]

You're welcome!
Much of the performance challenge will be that I have Excel 2016 so I don't have the UNIQUE function to populate column H.
If you've UNIQUE available it would run much faster.