Rearange data from sheet 1 on sheet 2

Hello,

I have a sheet of data, containing names, residence and some other stuff.

On sheet 2 i would like to get "tables?" so the names are grouped on residence.

See the test.xlsx to clarify what i mean. I have no clue on how things are named in excel so i have also no clue on what to search for in google.

Thanks,
Jazzery

See the attached file, where I have put this formula in D2 of Sheet1 (coloured blue):

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

and then copied this down to beyond your data. In Sheet2 I have arranged the tables to go across with the location headings in C1, F1, I1 and L1, and then used these formulae in the cells stated:

C2: =IFERROR(INDEX(Sheet1!$A:$A,MATCH(C$1&"_"&ROWS($1:1),Sheet1!$D:$D,0)),"")

D2: =IFERROR(INDEX(Sheet1!$C:$C,MATCH(C$1&"_"&ROWS($1:1),Sheet1!$D:$D,0)),"")

These formula can be copied down as far as required (until you start to get blanks), and then the block of formulae can be copied across to generate the other tables.

I've attached the file, so that you can see the formulae in your regional language.

Hope this helps.

Pete