Question description:
This user has given permission to use the problem statement for this
blog.
I have on Sheet1, a table with information Column A (Stores) and Column B (Product). In Sheet2 I want to put in a cell (Store) and underneath all the products of that store. Could help me with a formula, without having to filter by store and copying and pasting.
Solved by F. B. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/09/2018 - 04:19
Welcome to got it pro.
We are delighted to have you here with us.
User
06/09/2018 - 04:19
Hi
Excelchat Expert
06/09/2018 - 04:19
I believe I can help.
User
06/09/2018 - 04:19
Thank you
Excelchat Expert
06/09/2018 - 04:20
Would you be able to show me your excel file?
User
06/09/2018 - 04:20
My computer is encrypted and will not let me send files
Excelchat Expert
06/09/2018 - 04:21
Okay I understand.
Excelchat Expert
06/09/2018 - 04:21
I will write some dummy data in the blank spreadsheet on the right.
User
06/09/2018 - 04:21
Ok
User
06/09/2018 - 04:23
Yes, is there a formula that can write only the name of the store and below all the products appear?
Excelchat Expert
06/09/2018 - 04:23
Yeah. I'm currently writing that in sheet2.
User
06/09/2018 - 04:24
I'll wait
Excelchat Expert
06/09/2018 - 04:25
You are using excel right?
User
06/09/2018 - 04:25
Yes
Excelchat Expert
06/09/2018 - 04:26
Thanks for confirming. I'll work on my laptop's excel and share the file with you.
User
06/09/2018 - 04:26
Thank you
Excelchat Expert
06/09/2018 - 04:29
Typically how many rows would there be in your excel file?
User
06/09/2018 - 04:31
50
Excelchat Expert
06/09/2018 - 04:31
Alright, I'm adjusting the formula for 50 rows and sending the file to you.
User
06/09/2018 - 04:32
Thanks
Excelchat Expert
06/09/2018 - 04:33
[Uploaded an Excel file]
Excelchat Expert
06/09/2018 - 04:33
Please download the file.
Excelchat Expert
06/09/2018 - 04:33
The formula I wrote in column A sheet 2 is =IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,3,(Sheet1!$A$2:$A$51=$B$1)/(Sheet1!$A$2:$A$51=$B$1)*ROW(Sheet1!$A$2:$A$51),ROWS(Sheet1!$A$2:A2))),"")
User
06/09/2018 - 04:33
Already
Excelchat Expert
06/09/2018 - 04:34
assuming you would input the store name in Cell B1 of sheet2.
Excelchat Expert
06/09/2018 - 04:34
Column B formula =IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,3,(Sheet1!$A$2:$A$51=$B$1)/(Sheet1!$A$2:$A$51=$B$1)*ROW(Sheet1!$A$2:$A$51),ROWS(Sheet1!$A$2:A2))),"")
Excelchat Expert
06/09/2018 - 04:34
Please let me know if you have any question.
User
06/09/2018 - 04:35
let me try and let you know
Excelchat Expert
06/09/2018 - 04:35
Sure, take your time.
Excelchat Expert
06/09/2018 - 04:35
Please extend the chat by selecting YES.
Excelchat Expert
06/09/2018 - 04:35
Otherwise the chat will end in 3 minutes.
User
06/09/2018 - 04:36
and if it is greater than 50 rows?
Excelchat Expert
06/09/2018 - 04:37
Do you see the number 50 in the formula? Change it to your need.
Excelchat Expert
06/09/2018 - 04:38
Sorry I actually wrote 51 in the formula, so you need to change it if you have more rows.
User
06/09/2018 - 04:38
Ok
Excelchat Expert
06/09/2018 - 04:39
The reason I didn't accommodate more rows in the formula already is, this formula is pretty complex and adding more rows would make the file slower unnecessarily.
Excelchat Expert
06/09/2018 - 04:39
Makes sense?
User
06/09/2018 - 04:40
Yes
Excelchat Expert
06/09/2018 - 04:40
Great.
Excelchat Expert
06/09/2018 - 04:40
I'm here if you have more questions.
Excelchat Expert
06/09/2018 - 04:40
I mean more question regarding this particular problem. not another problem. :)
User
06/09/2018 - 04:45
It served me in a fantastic way, thanks for your help
Excelchat Expert
06/09/2018 - 04:45
I'm really glad to hear that.
Excelchat Expert
06/09/2018 - 04:46
I would be grateful if you can manage to rate me 5 star for my service.
User
06/09/2018 - 04:46
I replaced it with my data and it worked without problem
User
06/09/2018 - 04:46
Without a doubt
Excelchat Expert
06/09/2018 - 04:46
:) its like a magic, ain't it True!
Excelchat Expert
06/09/2018 - 04:47
Thanks for your patience. If you don't have any question, I would like to go. :)
Excelchat Expert
06/09/2018 - 04:47
Don't forget to visit us today and have a great day ahead!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
