Excel Formula for combining data into a single row

Copper Contributor

I am using an online registration platform for an event where people can select one or several items to purchase with their registration.  When I download the information (Excel format), the info is broken up into different tabs.  I need all of the info on one spreadsheet, which I could normally do in just a few minutes.  HOWEVER, there are two tabs that will list the individual "X" number of times based on....

  • per item purchased (i.e. package #1, #3, #5, #8 - this person would be listed 4 times)
  • per answer to a custom question (i.e. address, city, state, zip, phone, email - this person would be listed 6 times)

Each registration is different, depending on purchases and completed info to the custom questions.  I just need everything listed on one row per registrant.

 

Is there a way to do this so I don't have to re-enter everything manually? TIA

4 Replies

@CBay88 

=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.

combining tables.JPG

 

@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.Screenshot 2023-03-13 at 4.00.14 PM.png

@CBay88 

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.

customer name.JPG

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.