Find out missing name or entity is in between months

[James Clear]

How will I find out that Rahul name was present in Aug to Oct and then in Dec'21 , however same was missing in between Nov'21 month

Imagine there are thousands or lakhs of names and i have to find out such names with the months , how will i do it with excel formula ? pls assist

Name	Month
Rahul	Aug-21​
Rahul	Sep-21​
Rahul	Oct-21​
Atul	Nov-21​
Rahul	Dec-21​

 

[BSALV]

with a pivottable you can count unique or the number of times a person appears in a month

Map1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Month			Unieke telling van Name	Kolomlabels
2	Rahul	Aug-21			Rijlabels	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Eindtotaal
3	Rahul	Sep-21			Atul				1		1
4	Rahul	Oct-21			Rahul	1	1	1		1	1
5	Atul	Nov-21			Eindtotaal	1	1	1	1	1	2
6	Rahul	Dec-21
7
8
9
Blad2

 

[James Clear]

with a pivottable you can count unique or the number of times a person appears in a month

Map1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Month			Unieke telling van Name	Kolomlabels
2	Rahul	Aug-21			Rijlabels	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Eindtotaal
3	Rahul	Sep-21			Atul				1		1
4	Rahul	Oct-21			Rahul	1	1	1		1	1
5	Atul	Nov-21			Eindtotaal	1	1	1	1	1	2
6	Rahul	Dec-21
7
8
9
Blad2

Thank you for your response , however I am looking for some formula which will help me to establish this output

 

[BSALV]

if B is sorted ascending, then in order in E2:N2 'll be oké

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Month			unique names
2	Rahul	Aug-21		unique names	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
3	Rahul	Sep-21		Rahul	1	1	1		1
4	Rahul	Oct-21		Atul				1
5	Atul	Nov-21
6	Rahul	Dec-21
7
8
9
10
11
12
13
14
Blad2

Cell Formulas
Range	Formula
E2:N2	E2	=IFERROR(TRANSPOSE(INDEX(Tabel1[[Month]:[Month]], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[[Month]:[Month]]), 0, 0), 0))), "")
D3:D12	D3	=IFERROR(INDEX(Tabel1[Name], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[Name]), 0, 0), 0)), "")
E3:N12	E3	=SUMPRODUCT((Tabel1[[Name]:[Name]]=$D3)*(Tabel1[[Month]:[Month]]=E$2))

 

[James Clear]

if B is sorted ascending, then in order in E2:N2 'll be oké

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Month			unique names
2	Rahul	Aug-21		unique names	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
3	Rahul	Sep-21		Rahul	1	1	1		1
4	Rahul	Oct-21		Atul				1
5	Atul	Nov-21
6	Rahul	Dec-21
7
8
9
10
11
12
13
14
Blad2

Cell Formulas
Range	Formula
E2:N2	E2	=IFERROR(TRANSPOSE(INDEX(Tabel1[[Month]:[Month]], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[[Month]:[Month]]), 0, 0), 0))), "")
D3:D12	D3	=IFERROR(INDEX(Tabel1[Name], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[Name]), 0, 0), 0)), "")
E3:N12	E3	=SUMPRODUCT((Tabel1[[Name]:[Name]]=$D3)*(Tabel1[[Month]:[Month]]=E$2))

Can you please simplify or define - Table[Month]: [Month] into ranges for my better understanding

 

[James Clear]

if B is sorted ascending, then in order in E2:N2 'll be oké

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Month			unique names
2	Rahul	Aug-21		unique names	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
3	Rahul	Sep-21		Rahul	1	1	1		1
4	Rahul	Oct-21		Atul				1
5	Atul	Nov-21
6	Rahul	Dec-21
7
8
9
10
11
12
13
14
Blad2

Cell Formulas
Range	Formula
E2:N2	E2	=IFERROR(TRANSPOSE(INDEX(Tabel1[[Month]:[Month]], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[[Month]:[Month]]), 0, 0), 0))), "")
D3:D12	D3	=IFERROR(INDEX(Tabel1[Name], MATCH(0, INDEX(COUNTIF($D$2:D2, Tabel1[Name]), 0, 0), 0)), "")
E3:N12	E3	=SUMPRODUCT((Tabel1[[Name]:[Name]]=$D3)*(Tabel1[[Month]:[Month]]=E$2))

Thanks Mate!!

 

[BSALV]

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Month			unique names
2	Rahul	Aug-21		unique names	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
3	Rahul	Sep-21		Rahul	1	1	1		1
4	Rahul	Oct-21		Atul				1
5	Atul	Nov-21
6	Rahul	Dec-21
7
8
9
10
11
12
13
Blad2

Cell Formulas
Range	Formula
E2:N2	E2	=IFERROR(TRANSPOSE(INDEX($B$2:$B$6, MATCH(0, INDEX(COUNTIF($D$2:D2, $B$2:$B$6), 0, 0), 0))), "")
D3:D12	D3	=IFERROR(INDEX($A$2:$A$6, MATCH(0, INDEX(COUNTIF($D$2:D2, $A$2:$A$6), 0, 0), 0)), "")
E3:N12	E3	=SUMPRODUCT((Blad2!$A$2:$A$6=$D3)*(Blad2!$B$2:$B$6=E$2))

 

[James Clear]

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Month			unique names
2	Rahul	Aug-21		unique names	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
3	Rahul	Sep-21		Rahul	1	1	1		1
4	Rahul	Oct-21		Atul				1
5	Atul	Nov-21
6	Rahul	Dec-21
7
8
9
10
11
12
13
Blad2

Cell Formulas
Range	Formula
E2:N2	E2	=IFERROR(TRANSPOSE(INDEX($B$2:$B$6, MATCH(0, INDEX(COUNTIF($D$2:D2, $B$2:$B$6), 0, 0), 0))), "")
D3:D12	D3	=IFERROR(INDEX($A$2:$A$6, MATCH(0, INDEX(COUNTIF($D$2:D2, $A$2:$A$6), 0, 0), 0)), "")
E3:N12	E3	=SUMPRODUCT((Blad2!$A$2:$A$6=$D3)*(Blad2!$B$2:$B$6=E$2))

I m having 2.5L rows which r taking a huge time to calculate it..I m applying only sumproduct formula n even if I apply it after that I will have to apply the logic to find out missing month
For example
Sequence comes out to be 10111 then I would require to count this whereas 11111 this is fine so how can I derive this by applying one formula? Pls assist

 

[BSALV]

the final goal is a list with only person & month missing or for every person a string like "1111101111000111100".
Is VBA (macro) allowed ?

 

[BSALV]

assumption all the dates in column B are the 1st of that month, otherwise i have to add an extra check

Sub test()
     t = Timer
     Set dict = CreateObject("scripting.dictionary")
     dict.CompareMode = vbTextCompare

     a = Sheets("james").Range("A1").CurrentRegion.Value2       'read to an array
     mymin = Application.Min(Application.Index(a, 0, 2))        'smallest
     mymax = Application.Max(Application.Index(a, 0, 2))        'greatest
     Delta = WorksheetFunction.Round((mymax - mymin) / 30.5, 0) + 1     'number of months
     s0 = "'" & WorksheetFunction.Rept("0", Delta)              'startstring

     For i = 2 To UBound(a)
          If Not dict.exists(a(i, 1)) Then dict.Add a(i, 1), Array(a(i, 1), s0)     'does name already exist in dict, if not add
          it = dict(a(i, 1))                                    'item for that name
          j = WorksheetFunction.Round((a(i, 2) - mymin) / 30.5, 0) + 2     ' that month is character j in the string
          it(1) = Left(it(1), j - 1) & "1" & Mid(it(1), j + 1)  'replace that char with a "1"
          dict(a(i, 1)) = it                                    'write back to dict
     Next

     With Range("D1").Resize(dict.Count, 2)
          .Value = Application.Index(dict.items, 0, 0)
          .EntireColumn.AutoFit
     End With

     MsgBox "done in " & Format(Timer - t, "0.0s")
End Sub

Map1
	A	B	C	D	E																			X
1	Name	Month		Rahul	11100000000000001
2	Rahul	1/08/21		Atul	00010000000000000
3	Rahul	1/09/21
4	Rahul	1/10/21
5	Atul	1/11/21
6	Rahul	1/12/22
7
8
9
james