Find duplicates starting from today

[SamPaulRoger]

Hi,

I have two tables, one is a data output that I have to analyze, the other is a list of unique values where I need to know if there are any duplicates that fall between today's date and beyond.

Sheet1

7/25	7/26	7/27	7/28	7/29	7/30	7/31	8/1	8/2	8/3
ABC109	ABC100	ABC100		ABC100		DCV394	DEF101	DEF101	QVS001
ABC101	DEF102	DEF103		ABC101	QWE502		DEF102	DEF102
QWE501		FBB100	DCV393	ABC101			DEF103	DEF103

<tbody>
</tbody>

For example, assuming today is 7/27, the script would start counting duplicates and output the count in the table on Sheet2

ID #	Duplicates
ABC100	2
ABC101	2
FBB100	0
DEF100	0
DEF101	2
DEF102	2
DEF103	3
DCV393	0

<tbody>
</tbody>

 

[DRSteele]

I think this will do it. Your sample data seems to be providing a count only, so that's what I include.

Book1
	A	B	C	D	E	F	G	H	I
1	7/25/18	7/26/18	7/27/18	7/28/18	7/29/18	7/30/18	7/31/18	8/01/18	8/02/18
2	ABC109	ABC100	ABC100		ABC100		DCV394	DEF101	DEF101
3	ABC101	DEF102	DEF103		ABC101	QWE502		DEF102	DEF102
4	QWE501		FBB100	DCV393	ABC101			DEF103	DEF103
5
6	7/27/18
7	ID #	Count
8	ABC100	2
9	ABC101	2
10	FBB100	1
11	DEF100	0
12	DEF101	2
13	DEF102	2
14	DEF103	3
15	DCV393	1
Sheet23

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(($A$1:$J$1>=$A$6)*($A$2:$J$4=A8))

 

[SamPaulRoger]

Alright, so this worked, however, it is extremely slow for the actual data set, I am looking up 9000 values in a range that is A1:ZZ500, Is there any way to have this done faster with vba?

 

[FDibbins]

1. Are these real dates, or text dates?
2. I have a feeling that formulas will probably be faster than VBA

 

[kvsrinivasamurthy]

Sheet2 ID# list already existing or to be written by VBA.

 

[sandy666]

Try PowerQuery

result table:

Value	Count
ABC100	2​
DEF103	3​
ABC101	2​
DEF101	2​
DEF102	2​

M Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"25/07/2018", type text}, {"26/07/2018", type text}, {"27/07/2018", type text}, {"28/07/2018", type text}, {"29/07/2018", type text}, {"30/07/2018", type text}, {"31/07/2018", type text}, {"01/08/2018", type text}, {"02/08/2018", type text}, {"03/08/2018", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Column1", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each [Column1] >= #date(2018, 7, 27)),
    #"Kept Duplicates" = let columnNames = {"Value"}, addCount = Table.Group(#"Filtered Rows", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Filtered Rows", columnNames, removeCount, columnNames, JoinKind.Inner),
    #"Grouped Rows" = Table.Group(#"Kept Duplicates", {"Value"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Peter_SSs]

Duplicate to your earlier thread: https://www.mrexcel.com/forum/excel-questions/1064876-horizontal-lookup.html

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the original thread.