Forum Discussion
Combine multiple results in to 1 row
You have a table in Excel with multiple rows for the same customer and you want to combine these rows into one row per customer while retaining the dates for each product topic. Is that correct?
If so, one way to achieve this is by using a PivotTable.
A PivotTable allows you to summarize and organize your data based on specific criteria. In your case, you could create a PivotTable with the Account column as the Rows field and the Product columns as the Columns field. This would group all the rows for each customer together and display the dates for each product topic in separate columns.
It is possible to use formulas to combine rows by customer while retaining the date for each product topic, but it can be quite complex and may require the use of multiple formulas and helper columns. Using a PivotTable, as described, is a simpler and more straightforward way to achieve the desired result.
That's right. I've used a pivot table but it's very limited in the way It sorts or doesn't allow me to sort by different columns.
- If you find that the sorting options in a PivotTable are limited, you can try using a formula to sort your data instead. One way to do this is by using the INDEX and MATCH functions together. These functions allow you to return a value from a specified row and column in a range of cells based on certain criteria.
For example, if you have a table with customer names in column A, product topics in column B, and dates in column C, you could use the following formula to sort the table by customer name and then by date:
=INDEX($A$2:$C$6,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$6&"-"&$C$2:$C$6),0),{1,2,3})
This formula uses the INDEX function to return the values from columns A, B, and C for the row that matches the criteria specified by the MATCH function. The MATCH function searches for the first row where the customer name and date concatenated with a hyphen (-) have not already appeared in column E (which is where the sorted data will be displayed) and returns the row number to the INDEX function.Hi,
I found that the pivot table was throwing out some incorrect results so I started again.I've used the phrase table a couple of times but I mean the cells with the relevant info. I haven't actually formated or created any tables.
I started by sorting my imported data from an import sheet onto a separate worksheet into date order with the newest results at the top so that an Xlookup would always return the newest result.
=SORT(FILTER(A2:D7001,A2:A7001<>""),4,-1)
I then referenced the sorted data above as below and used helper rows to add a searchable index code.
in cell L2 =IF(F2="","",F2) F2 being the account number on the sorted table above
cell O2 =IF($H2=O$1,$I2,"")
H2 being the 1st result from the sorted table above that is matches the topic in O1 in the table below.
Index code in cell N2 =IF(O2="","",$L2&O$1)
I was then able to consolidate everything into 1 unique table showing only the newest contacts per customer per topic as below.
Cell AI =SORT(UNIQUE(FILTER(L2:L7001,L2:L7001<>"")))
Cell AK2 =XLOOKUP($AI2&AK$1,N:N,O:O,"")
Cell AK3 =XLOOKUP($AI2&AL$1,P:P,Q:Q,"")
And so on and then the date of the latest contact in Cell AU2
=IF(SUM(AK2:AT2)<=1,"",MAX(AK2:AT2))
I then created 2 lists AX1 to AX9 and AZ1 to AZ3 to use as sort options on a results worksheet.
AY1 to AY9 is the column to sort by and BA1 to BA3 is the sort order.
BC1 references a dropdown on the results worksheet then looks up the number to use in the sort function
=XLOOKUP(Results!O2,Worksheet!AX1:AX12,Worksheet!AY1:AY12)
BC2 does the same to find the other number to use in the same sort function
=XLOOKUP(Results!R2,Worksheet!AZ1:AZ3,Worksheet!BA1:BA3)
This then gives me the table below that can be sorted by topic / account / last contact etc and sort order by selecting the options in the dropdown in O2 and R2.
I've hidden the account names for data confidentiality reasons. I hope I've explained this well.
- If possible,upload your Excel file with dummy data.
If you run Excel >/= 2016 / Windows there's probably something to do with Get & Transform aka Power Query
Glad to have a look at this if you can share a representative workbook + an example of the expected result as I must admit it's not clear to me
- HI,
I've done it. I used a pivot table to get everything together. Then on a different worksheet I used Sort & Filter referencing another cell to change the sort order. Thanks for the suggestions.- Hi
Glad you now have something that meets you need & Thanks for documenting your solution
Nice day...
