Find multiple repeated values and copy rows

Question

I need to pull repeated values from a table and copy information from my Row 2 as many times as there are items returned. The example makes more sense. Here is a truncated example of the table with my data:

This is what I want to pull it into:

And the desired outcome:

My hope is I could enter "food" into the first Category cell, a name into the first Name cell. If I have 170 things in the "food" Category, it would have 170 lines of "food," 170 prices, 170 items (in my list the items are all unique), and 170 lines of "Megan," or whatever name I've entered there.

I can use INDEX and MATCH to fill out the first line of Price and Item, but if I repeat that formula it just gives me the same thing in every row. I've found ways to almost do what I want with SMALL, LARGE, and COUNTIF, but all of those return the entire list, ordered. That would mean I would have to go through and delete all the "shoes" and "books." Each of my categories will be saved in a separate file.

The table can be sorted A to Z. Perhaps there's a way to find "food," COUNTIF(?) how many times it appears, and then copy that many cells from the table to my new table? In other words, "food" first appears in A2, appears 170 times, copy everything from Table1 cells A2 to A172, print in Table2 cells A2 to A172, repeat value in D2 ["Megan"] 170 times?

UPDATE: I figured out a workaround but it could be better. I would love to find a way to pull in all the 1000+ of each item from the pivot table, rather than having to select them all by hand. This is what I'm doing now:

I put the first table's values into a pivot table, then I selected all of the "items" in each category and pasted them into my new table.

Thanks to this question, I used =IF($C2<>"",D$2,"") to fill the rows with the appropriate data, where C is Item and D is Name. It's not exactly "automated," as I had to drag the formula down 1100 cells, but all I have to do is paste the individual Items from my first table and column D copies itself.

For Price, I used this: =IFERROR(INDEX(Table1[Price],MATCH([Item],Table1[Item],0)),"")

Similar formula for Category. Again, not exactly automated.

Answer 1

for a formula you might want to use this: ARRAY FORMULA: CTRL + SHIFT +ENTER

=IFERROR(INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$E2,ROW($A$1:$A$7)),ROW(A1)),MATCH(F$1,$A$1:$C$1,0)),"")

You can adapt the ranges to your needs. I copied the formula to F2 and pulled it sideways and downwards. If you need help, just say so :)

Is there any condition for Megan?

Answer 2

You should consider use power query. https://www.contextures.com/excelpowerquerycombinetables.html

This should work.

you can create the queries table for the price items and the name.

I created tbl_item and tbl_name as below.

Then i go to New Query > Combine Queries > Merge. Which should like the figure below. highlight the column where you want to base on to merge. in this case is based on category.

Click OK and you will have what you want.

Hope this help.