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