lookup stuff

[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

[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

[sna]

Thanks.how about excel 365 formula?

[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

[sna]

Thank you so much