Count occurrence of unknown text strings

[mkl1]

HI I am looking to prepare an analysis of text occurrence where the text is not known upfront.

So essentially it will spot, find and report the occurrence that were not there in the previous report - they change with each result set.

So for instance if the first report had:

AAAB1
AAAB1
AAAB2
AAAB3

Then the second report had
AAAB1
AAAB2
AAAC1
AAAB2
AAAB3

I want the analysis to tell me what appeared and how many times - without my having to tell it up front which codes will appear. Is there a way to do that?

Many thanks

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are those two reports in the same workbook?, Are they on the same worksheet?

What are the expected results for that sample data and please give any further clarification that you can about those expected results.

Does it matter whether any suggested solution uses formulas/vba/power query?

 

[JamesCanale]

If you have 365:

MrExcelPlayground2.xlsx
	A	B	C	D
1	Report 1	Report 2		New Item - Count
2	AAAB1	AAAB1		AAAC1-2
3	AAAB1	AAAB2		AAAB4-1
4	AAAB2	AAAC1
5	AAAB3	AAAB2
6		AAAB3
7		AAAC1
8		AAAB4
Sheet26

Cell Formulas
Range	Formula
D2:D3	D2	=FILTER(UNIQUE(B2:B8)&"-"&IF(COUNTIF($A$2:$A$5,UNIQUE(B2:B8))=0,COUNTIF($B$2:$B$8,UNIQUE(B2:B8)),""),IF(COUNTIF($A$2:$A$5,UNIQUE(B2:B8))=0,COUNTIF($B$2:$B$8,UNIQUE(B2:B8)),"")<>"","")
Dynamic array formulas.

 

[mkl1]

If you have 365:

MrExcelPlayground2.xlsx
	A	B	C	D
1	Report 1	Report 2		New Item - Count
2	AAAB1	AAAB1		AAAC1-2
3	AAAB1	AAAB2		AAAB4-1
4	AAAB2	AAAC1
5	AAAB3	AAAB2
6		AAAB3
7		AAAC1
8		AAAB4
Sheet26

Cell Formulas
Range	Formula
D2:D3	D2	=FILTER(UNIQUE(B2:B8)&"-"&IF(COUNTIF($A$2:$A$5,UNIQUE(B2:B8))=0,COUNTIF($B$2:$B$8,UNIQUE(B2:B8)),""),IF(COUNTIF($A$2:$A$5,UNIQUE(B2:B8))=0,COUNTIF($B$2:$B$8,UNIQUE(B2:B8)),"")<>"","")
Dynamic array formulas.

This is great - solved the issue! Thanks a lot!

 

[alansidman]

An alternative solution is to bring each table into Power Query and then run a right anti merge. Here is the Mcode

let
    Source = Table.NestedJoin(Table1, {"Report 1"}, Table2, {"Report 2"}, "Table2", JoinKind.RightAnti),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Report 2"}, {"Table2.Report 2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Report 1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Table2.Report 2"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book3
	A	B
1	Table2.Report 2	Count
2	AAAC1	2
3	AAAB4	1
Merge1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Peter_SSs]

Thanks for updating your profile.

What about two fairly simple formulas like this?

21 05 24.xlsm
	A	B	C	D	E
1	Report 1	Report 2		New Items	Count
2	AAAB1	AAAB1		AAAC1	2
3	AAAB1	AAAB2		AAAB4	1
4	AAAB2	AAAC1
5	AAAB3	AAAB2
6		AAAB3
7		AAAC1
8		AAAB4
9
New

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(FILTER(B2:B8,ISNA(MATCH(B2:B8,A2:A5,0)),""))
E2:E3	E2	=COUNTIF(B2:B8,D2#)
Dynamic array formulas.