Forum Discussion
Combine multiple results in to 1 row
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.
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.
