Compare and count from multiple sheets

[dininaus]

Hi All,


Could someone please help me with the query below


I have 3 sheets Labels, Labels and Categories and Results. I have to check the all the values in Labels sheet against Labels and Categories sheet and report the total numbers in Result sheet. Please see example below.


Thanks in advance!

Labels sheet

[TABLE="width: 247"]
<tbody>[TR]
[TD]Label1[/TD]
[TD]Label2[/TD]
[TD]Label3[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]12[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]12345[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Labels and Categories sheet

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Label[/TD]
[TD="width: 64"]Category[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]1234[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]C[/TD]
[/TR]
</tbody>[/TABLE]


Results Sheet

[TABLE="width: 223"]
<tbody>[TR]
[TD]Categories[/TD]
[TD]Total Labels[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[sandy666]

with Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Label1[/td][td=bgcolor:#5B9BD5]Label2[/td][td=bgcolor:#5B9BD5]Label3[/td][td][/td][td=bgcolor:#5B9BD5]Label[/td][td=bgcolor:#5B9BD5]Category[/td][td][/td][td=bgcolor:#70AD47]Category[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7]

12345​

[/td][td][/td][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7]A[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1234​

[/td][td]

12​

[/td][td]

1234​

[/td][td][/td][td]

1234​

[/td][td]B[/td][td][/td][td]B[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#DDEBF7]

12​

[/td][td=bgcolor:#DDEBF7]C[/td][td][/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]
[/table]

[SIZE=1]// Category
let
    Source = Excel.CurrentWorkbook(){[Name="Category"]}[Content]
in
    Source[/SIZE]

[SIZE=1]// Labels
let
    Source = Excel.CurrentWorkbook(){[Name="Labels"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"[/SIZE]

[SIZE=1]// Merge1
let
    Source = Table.NestedJoin(Category,{"Label"},Labels,{"Value"},"Labels",JoinKind.LeftOuter),
    #"Expanded Labels" = Table.ExpandTableColumn(Source, "Labels", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Expanded Labels", {"Category"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Peter_SSs]

Excel 2016
	A	B	C
1	Label1	Label2	Label3
2	12345	12345	12345
3	1234	12	1234
4	12345	12345
Labels

Excel 2016
	A	B
1	Label	Category
2	12345	A
3	1234	B
4	12	C
Labels & Categories

B2 copied down

Excel 2016
	A	B
1	Categories	Total Labels
2	A	5
3	B	2
4	C	1
Results

Cell Formulas
Range	Formula
B2	=COUNTIF(Labels!$A$2:$C$4,INDEX('Labels & Categories'!$A$2:$A$4,MATCH(A2,'Labels & Categories'!$B$2:$B$4,0)))

 

[dininaus]

Awesome Thanks Guys for your help!

 

[sandy666]

you are welcome

have a nice day

 

[Peter_SSs]

Awesome Thanks Guys for your help!

You're welcome. Glad you got something that suited your purpose.