Unique values for specified range

[aboodbata]

I have a master sheet with thousands of rows. This sheet is basically the mother sheet where other sheets will be taking their data from. Let's take the following mother table for example:




Now I have another sheet which is exclusively for musical events. The table looks like this:






Basically, what I need is a formula in A3, that will extract only unique musical events from the mother sheet.


I know this can be achieved using a pivot table, but I prefer the formula method, which would probably involve index and match.


Thank you.

 

[Johnny C]

I can't see your images (blocked at work). However it's straightforward with formulae. You need a helper column. In that column look for the value in the column where it's identified as a unique musical event.

Say your Master sheet helper column is K, from row 2 down

=IF( {see if it's a unique musical event}, MAX($K$1:$K{Row - 1})+1,"")

Then in your other worksheet column A is 1,2,3...
in column B is

    =IFERROR(MATCH($A{row},'Master sheet.xlsx'!$K:$K,0)-1,"")

and in columns C onwards

    =IF($B{row}="","",OFFSET('Master sheet.xlsx'!$B$1,$B{row},0))

Where {row} is the row number of the cell and 'Master sheet.xlsx'!$B$1 is the top row of each of the columns you need from the master

No doubt you could use INDEX but I personally don't use it, it's easier to see if something goes wrong with OFFSET (and you can do a lot more).
The downside to OFFSET is that if your Master Sheet isn't open it will return # REF ! errors.

HTH

 

[aboodbata]

I can't see your images (blocked at work). However it's straightforward with formulae. You need a helper column. In that column look for the value in the column where it's identified as a unique musical event.

Say your Master sheet helper column is K, from row 2 down

=IF( {see if it's a unique musical event}, MAX($K$1:$K{Row - 1})+1,"")

Then in your other worksheet column A is 1,2,3...
in column B is

    =IFERROR(MATCH($A{row},'Master sheet.xlsx'!$K:$K,0)-1,"")

and in columns C onwards

    =IF($B{row}="","",OFFSET('Master sheet.xlsx'!$B$1,$B{row},0))

Where {row} is the row number of the cell and 'Master sheet.xlsx'!$B$1 is the top row of each of the columns you need from the master

No doubt you could use INDEX but I personally don't use it, it's easier to see if something goes wrong with OFFSET (and you can do a lot more).
The downside to OFFSET is that if your Master Sheet isn't open it will return # REF ! errors.

HTH

Thank you very much for helping me out, but I don't quite understand your explanation. Therefore, please take a look at the following pictures and give me the exact formulas. And btw, all the information is contained in the same workbook, just different sheets.





Thank you.

 

[Johnny C]

I can't see your sheets, imgur (and all file sharing sites) are blocked on my work PC. Sorry I can't help any more.

 

[Aladin Akyurek]

Care to post the outcome for the initial data you posted?