Forum Discussion
Excel Formula for combining data into a single row
=IFERROR(INDEX($F$2:$F$19,SMALL(IF($E$2:$E$19=$H2,ROW($E$2:$E$19)-1),COLUMN(A$1))),"")=IFERROR(INDEX($A$2:$A$23,SMALL(IF($B$2:$B$23=$H2,ROW($B$2:$B$23)-1),COLUMN(A$1))),"")For the data layout of the example you can try these formulas. The formulas have to entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. The first formula is in cell I2 and filled across range I2:L4. The second formula is in cell M2 and filled across range M2:T4.
OliverScheurich Sorry, but I'm not sure I am following this correctly. I thought it would have to be some sort of if/and statement with a vlookup???? Here is an example of the what I'm trying to organize. This tab lists the individual multiple times....one for each purchase they've made. But I want all of the info per person on one row. I hope that makes sense.
In the previous suggestion i tried to make up a scenario with two tabs each with several information for each registrant. Unfortunately i misunderstood what you want to do.
For your actual data you can try Power Query. Does the green result table show the expected result?
In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.
- Wow...okay, I'm starting to think this is well beyond my level of understanding and ability. I see the data updating, but I can't tell how you did that. I think I may just have to settle for manual entry. Thank you for helping...this really would be a great tool if I were able to wrap my head around the steps.