Combine multiple results in to 1 row

[AndyT410]

Hi,

I have a table with rep contacts on different product topics on different days to the same customer as below which are on different rows dependent on the date. Is there a formula that I could us to combine the rows by customer but still retain the date for each product topic. Thanks in advance.

[#2]

@AndyT410 

 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.

[#3]

Thanks Nikolino,
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.

[#4]

@AndyT410 

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

[#5]

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.

[#6]

Hi
Glad you now have something that meets you need & Thanks for documenting your solution
Nice day...

[#7]

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.

[#8]

@NikolinoDE 

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.

[#9]

If possible,upload your Excel file with dummy data.

[#10]

Attached

[#11]

@AndyT410 - Clatifications please:

#1 Data in sheet 'Import'?

#2 Expected in sheet 'Result'?

[#12]

The data in Import is a list of rep visits by date and the products they discussed. In that form it's unusable as I needed to show all interactions per customer by product.
The data in the Results sheet is the most recent contact per customer on every product that has been discussed. This can then be filtered by product, account, and last contact both oldest to newest and newest to oldest.

[#13]

Hi @AndyT410 

This doesn't well answer my questions (might be my fault) so let's see if the attached Power Query approach goes in the right direction

- Table 'Import' is the content of your 'Import' sheet formatted as Table

- Table 'Result' reflects what I understood in terms of expected result

Let me know where I missed something/what's wrong

[#14]

The result should be the company listed only once down the table with the most reason contact per topic listed across the rows.

[#15]

@AndyT410 

The result should be the company listed only once down the table with the most reason contact per topic listed across the rows

- company listed only once Then how do you expect to represent i.e. Company 12 that has [Account Number] 1245 and 1246 and 1246?

- most reason contact Does this mean Most recent [Call Made On]?

[#16]

That's an error on the sample doc. I used a quick formula to anonymise all of the data and it's thrown out some errors by the looks of it. There should only ever be 1 company with 1 account number. The most recent contact is as you say the last time a call was made.

[#17]

@AndyT410 

Could you check the attached query and let me know what's wrong/missing?

[#18]

Looks right to me, Just need the last contact column. I've deleted the old and reattached an amended base sample

[#19]

@AndyT410 

Looks right to me, Just need the last contact column. I've deleted the old and reattached an amended base sample

Well, I'm not going to do the job twice as in the last workbook I shared I already fixed the [Customer]s that had several [Account Number]s (i.e. Customer 12). That said no problem to add a [Last Contact] column if you wish but...

IF the Power Query option I shared does it, you shouldn't need it. I haven't checked all but in the attached workbook you'll find in 'PQ vs PVT' the output of the query vs. a PivotTable. A quick comparison gives me the impression they produce the same results and by activating the Grand Total fos rows you get the extra column you expect. Last but not least a Pivot Table approach will be more efficient

Let me know your thoughts and any issue

[#20]

Thanks. Looks great. My company doesn't have power query installed and I'm not allowed to install it. But thanks anyway. It looks a lot simpler.