Distilling down info from multiple rows

D

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
77
Hi folks,

I am wondering is there a way to summarise the table below so that it lets me know if an event had only newcomers, only returns, or returns and newcomers.

For instance, I am trying to distill this table down to:

On 11/11/19, the "Shop" had "New and Return" customers while the "Show" had only "Return" customers and the "Online" customers were all "new".

I'm not sure if this can be done with a pivot table or by using a mixture of formulas, but I've spent a couple of hours trying to figure this out!

Many thanks in advance,
Dave
[TABLE="width: 224"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 277"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Event[/TD]
[TD] Date[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Walk-in New[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Walk-in New[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Return[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Return[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Return[/TD]
[TD]Shop[/TD]
[TD="align: right"]11/11/2019[/TD]
[/TR]
[TR]
[TD]Return[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]
Return​
[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]
Return​
[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]
Return​
[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]
Return​
[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]Return[/TD]
[TD]Show[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]Online[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]Online[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]Online[/TD]
[TD="align: right"]
11/11/2019​
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Type[/td][td=bgcolor:#5B9BD5]Event[/td][td=bgcolor:#5B9BD5]Date[/td][td][/td][td]PivotTable[/td][td][/td][td][/td][td][/td][td]PowerQueryTable[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]New[/td][td=bgcolor:#DDEBF7]Shop[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td=bgcolor:#DDEBF7]Event[/td][td=bgcolor:#DDEBF7]Type[/td][td=bgcolor:#DDEBF7]Date[/td][td][/td][td=bgcolor:#70AD47]Event[/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td]New[/td][td]Shop[/td][td]
11/11/2019​
[/td][td][/td][td]Online[/td][td]New[/td][td]
11/11/2019​
[/td][td][/td][td=bgcolor:#E2EFDA]Online[/td][td=bgcolor:#E2EFDA]New[/td][td=bgcolor:#E2EFDA]
11/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Walk-in New[/td][td=bgcolor:#DDEBF7]Shop[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td]Shop[/td][td]New[/td][td]
11/11/2019​
[/td][td][/td][td]Shop[/td][td]New[/td][td]
11/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Walk-in New[/td][td]Shop[/td][td]
11/11/2019​
[/td][td][/td][td]Shop[/td][td]Return[/td][td]
11/11/2019​
[/td][td][/td][td=bgcolor:#E2EFDA]Shop[/td][td=bgcolor:#E2EFDA]Return[/td][td=bgcolor:#E2EFDA]
11/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Return[/td][td=bgcolor:#DDEBF7]Shop[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td]Shop[/td][td]Walk-in New[/td][td]
11/11/2019​
[/td][td][/td][td]Shop[/td][td]Walk-in New[/td][td]
11/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Return[/td][td]Shop[/td][td]
11/11/2019​
[/td][td][/td][td]Show[/td][td]Return[/td][td]
11/11/2019​
[/td][td][/td][td=bgcolor:#E2EFDA]Show[/td][td=bgcolor:#E2EFDA]Return[/td][td=bgcolor:#E2EFDA]
11/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Return[/td][td=bgcolor:#DDEBF7]Shop[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Return[/td][td]Show[/td][td]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Return[/td][td=bgcolor:#DDEBF7]Show[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Return[/td][td]Show[/td][td]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Return[/td][td=bgcolor:#DDEBF7]Show[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Return[/td][td]Show[/td][td]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Return[/td][td=bgcolor:#DDEBF7]Show[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]New[/td][td]Online[/td][td]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]New[/td][td=bgcolor:#DDEBF7]Online[/td][td=bgcolor:#DDEBF7]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]New[/td][td]Online[/td][td]
11/11/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

M code for PowerQuery
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Event", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Event", "Date"}, {{"Count", each _, type table}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Type", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Event", "Type", "Date", "Count"})
in
    #"Reordered Columns"[/SIZE]
 
Last edited:
Upvote 0
Cannot get image to post correctly but suggest a pivot table with a count of events by date.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
249
floW_5
F
W
  • Solved
How to count all the headlines listed in column CT between dates of July 1 and July 31 2019 listed in column CT
2
Replies
10
Views
1K
wmichael
W
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
804
Viau1337
V
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
M
  • Question Question
Finding a word in a cell and looking down that column and returning a result based on 2 pieces of criteria - Really stuck!
Replies
1
Views
647
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top