return multiple rows from one key

[fjbourne]

Hi, I have a table that has 5 different keys in column A. The keys are duplicated, but the data in the corresponding rows is not. I want to create a new tab for each of the 5 keys, and pull in the corresponding rows of data for each key including duplicates. I've watched a bunch of videos and searched the web but can't figure out how to do it easily. I attached the workbook. Thanks for any help!

[cbatrody]

Welcome to the Forum.

Try the following array formula in A4 (StarterPG):

Please Login or Register  to view this content.

To be confirmed by pressing CTRL+SHIFT+ENTER

See the attached file.

[fjbourne]

Perfect! Thanks!

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

[fjbourne]

Hi, I'm attempting to do something very similar with another file but I can't seem to get the array to work. I attached the file. Thanks!

[fjbourne]

Still can't seem to get the array formula working. Anyone?

[cbatrody]

Hi,

Modify the formula in A4 (ALLPG tab) as below:

Please Login or Register  to view this content.

Array entered, to be confirmed by pressing CTRL+SHIFT+ENTER

See the attached file.

[fjbourne]

Thanks! Unfortunately I'm having trouble modifying the formula to get it work in the other tabs- ALLSG, ALLSF, etc.

[popipipo]

Please Login or Register  to view this content.

Remove ALLPG!

Please Login or Register  to view this content.

[fjbourne]

that doesnt seem to work :/

[cbatrody]

Try modifying the formula in A3 (Starters Tab) as following:

=IFERROR(AJ3&U3,"")

drag this down the cells till the end.


Now your earlier formula on cell A4 (ALLPG tab) should work.

Please Login or Register  to view this content.

[fjbourne]

Thanks for all the suggestions, but I still can't get the other tabs to work with those formulas. I have the ALLPG tab working correctly, but I cant modify the code to get it to work in the other tabs. I attached the file. I want to use a different key in cell a1 in the other tabs. Thanks for all the help!

[popipipo]

I think cel A1 is not correct and should be 'ATLSG' and not 'ALLSG'

[fjbourne]

That was it, what a silly mistake. Thanks!

[fjbourne]

Can someone help me set up a similar Array with the same goal in mind on this file? I have 5 different variables ("QB","RB", "WR", "SWR", "TE") in each row that are located in column D on the "Master" tab. I want to pull in the entire row of data to the corresponding tab that matches the variable. I pasted in template rows on each tab. Thanks!!

[fjbourne]

I tried modifying the file and formula from one of my other files but it still isn't working.

=IFERROR(INDEX(master!A$1:A$288,SMALL(IF(master!$A$1:$A$288=$A$1,ROW(master!$A$1:$A$288)),ROW($A1))),"")

Seems like it should work but it's not returning anything.

Thanks!

[FDibbins]

I know this was your thread, but it is now over a year old, I suggest you start a new thread, and ref this 1 if needed.