need formula function or VBA to get the report as attached

Need your help to segregate the data as per the details given in the attached spread sheet(Sheet2 and Sheet3).

1. In Raw data sheet I have numbers in column A and address in column B for each number but again I have some unwanted details in column B for each number.
2. What I need is in Sheet 2, for each number where ever the cell starts with Location then in the C column needs to be update as "Address 1" and below that 2 cells needs to be filled as "Address2" and "Address3".
3. In Sheet 3, I need number in A column, Address 1 in B column, Address2 in C column and Address 3 in D column.

Note: some times we will get same number but the location address will be different.

Kindly help me on how to handle this.....

Try

in Sheet2 C2

=IF(LEFT('Raw Data'!$B2,8)="Location","Address 1",IF($C1="Address 1","Address 2",IF($C1="Address 2","Address 3","")))

Copy down

in Sheet3

in A2

=IFERROR(INDEX(Sheet2!$A$2:$A$98,SMALL(IF(Sheet2!$C$2:$C$98="Address 1",ROW(Sheet2!$A$2:$A$98)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

in B2 and copy across to D and down

=IFERROR(INDEX(Sheet2!$B$2:$B$98,SMALL(IF(Sheet2!$C$2:$C$98=B$1,ROW(Sheet2!$A$2:$A$98)-ROW($A$2)+1,""),ROWS($A$2:B2))),"")

All entries in Sheet3 are ...


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thank you it helped me a lot but please help to understand why we need to use SMALL and ROW in index function on sheet 3.