Page 1 of 1
lookup stuff
Posted: Tue Jul 25, 2023 8:20 pm
by sna
Hi there!
I need your help how we get the results like the sheet highlighted.
Is there any formula or tool to get ?
I attached a template
Thanks
Re: lookup stuff
Posted: Tue Jul 25, 2023 9:49 pm
by norkaz
...
By the tricks, try this.
Insert column "B"
B3:Bxx
=IF(C3="","",IF(C2="",A2,B2))
Then use the Data Filter by skipping "Blanks"
=========
If you need the formulas, try this.
Sheet "output"
A2
=IFERROR(INDEX('Raw Data'!A$2:A$999,AGGREGATE(15,6,ROW('Raw Data'!$B$2:$B$999)/('Raw Data'!$B$2:$B$999<>"")-ROW('Raw Data'!$B$2)+1,ROWS(A$2:A2))),"")
B2
=IFERROR(INDEX(LOOKUP(ROW('Raw Data'!$A$2:$A$999),ROW('Raw Data'!$A$2:$A$999)/(--LEFT('Raw Data'!$A$2:$A$999,3)>0),'Raw Data'!$A$2:$A$999),AGGREGATE(15,6,ROW('Raw Data'!$B$2:$B$999)/('Raw Data'!$B$2:$B$999<>"")-ROW('Raw Data'!$B$2)+1,ROWS(B$2:B2))),"")
C2:Fxx
=IFERROR(INDEX('Raw Data'!B$2:B$999,AGGREGATE(15,6,ROW('Raw Data'!$B$2:$B$999)/('Raw Data'!$B$2:$B$999<>"")-ROW('Raw Data'!$B$2)+1,ROWS(C$2:C2))),"")
Norkaz
Re: lookup stuff
Posted: Wed Jul 26, 2023 7:03 am
by sna
Thanks.how about excel 365 formula?
Re: lookup stuff
Posted: Wed Jul 26, 2023 10:09 am
by norkaz
...
A2
=FILTER(HSTACK('Raw Data'!A2:A13,LOOKUP(ROW('Raw Data'!A2:A13),ROW('Raw Data'!A2:A13)/(--LEFT('Raw Data'!A2:A13,3)>0),'Raw Data'!A2:A13),'Raw Data'!B2:E13),ISERROR(--LEFT('Raw Data'!A2:A13,3)))
Norkaz
Re: lookup stuff
Posted: Wed Jul 26, 2023 10:23 am
by sna
Thank you so much