Generate Batch ID

[arnabmit]

Hi,

I am trying to generate Batch ID based on Course, Date, & Time. I have managed to get result on sample data, but when I put it in 5000+ rows, it doesn't seem to work (no longer increments batch number based on unique criteria)

• Am I going about in an un-necessarily long-winded way?
• Is there an easy way to do this?
• Can this be done without the helper column?

No VBA, only Excel 2016 formula, please.

BatchID.xlsx
	A	B	C	D	E	F	G
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID
2	1234	Abc	Word	1-Sep	9:00	1	WOR001
3	2345	Abc	Word	1-Sep	9:00	0	WOR001
4	3456	Def	PowerPoint	1-Sep	9:00	1	POW001
5	4567	Abc	Word	1-Sep	12:00	1	WOR002
6	5678	Def	PowerPoint	1-Sep	12:00	1	POW002
7	6789	Xyz	Excel	1-Sep	12:00	1	EXC001
8	7890	Abc	Word	1-Sep	12:00	0	WOR002
9	8901	Xyz	Excel	1-Sep	12:00	0	EXC001
10	9012	Def	PowerPoint	1-Sep	12:00	0	POW002
11	1234	Xyz	Excel	2-Sep	9:00	1	EXC002
12	2345	Def	PowerPoint	2-Sep	12:00	1	POW003
13	3456	Abc	Word	2-Sep	12:00	1	WOR003
14	4567	Xyz	Excel	2-Sep	9:00	0	EXC002
15	5678	Def	PowerPoint	2-Sep	12:00	0	POW003
16	6789	Abc	Word	2-Sep	12:00	0	WOR003
17	7890	Xyz	Excel	2-Sep	12:00	1	EXC003
18	8901	Abc	Word	2-Sep	12:00	0	WOR003
19	9012	Xyz	Excel	2-Sep	12:00	0	EXC003
20	1234	Def	PowerPoint	3-Sep	15:00	1	POW004
21	2345	Xyz	Excel	3-Sep	9:00	1	EXC004
22	3456	Abc	Word	3-Sep	15:00	1	WOR004
23	4567	Xyz	Excel	3-Sep	9:00	0	EXC004
24	5678	Xyz	Excel	3-Sep	12:00	1	EXC005
25	6789	Abc	Word	3-Sep	15:00	0	WOR004
26	7890	Xyz	Excel	3-Sep	12:00	0	EXC005
27	8901	Def	PowerPoint	3-Sep	15:00	0	POW004
28	9012	Abc	Word	3-Sep	15:00	0	WOR004
Sheet1

Cell Formulas
Range	Formula
F2:F28	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G28	G2	=UPPER(LEFT(C2,3))&TEXT(SUMPRODUCT(($C$2:C2=C2)*($F$2:F2=1)),"000")

 

[jmacleary]

Hi there

Why not just concatenate the 3 variables then its bound to be unique.

 

[jmacleary]

e.g.:

Book1
	A	B	C	D	E	F	G
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID
2	1234	Abc	Word	1.9.20	09:00:00	1	Word01092009:00
3	2345	Abc	Word	1.9.20	09:00:00	0	Word01092009:00
4	3456	Def	PowerPoint	1.9.20	09:00:00	1	PowerPoint01092009:00
5	4567	Abc	Word	1.9.20	12:00:00	1	Word01092012:00
6	5678	Def	PowerPoint	1.9.20	12:00:00	1	PowerPoint01092012:00
7	6789	Xyz	Excel	1.9.20	12:00:00	1	Excel01092012:00
8	7890	Abc	Word	1.9.20	12:00:00	0	Word01092012:00
9	8901	Xyz	Excel	1.9.20	12:00:00	0	Excel01092012:00
10	9012	Def	PowerPoint	1.9.20	12:00:00	0	PowerPoint01092012:00
11	1234	Xyz	Excel	2.9.20	09:00:00	1	Excel02092009:00
12	2345	Def	PowerPoint	2.9.20	12:00:00	1	PowerPoint02092012:00
13	3456	Abc	Word	2.9.20	12:00:00	1	Word02092012:00
14	4567	Xyz	Excel	2.9.20	09:00:00	0	Excel02092009:00
15	5678	Def	PowerPoint	2.9.20	12:00:00	0	PowerPoint02092012:00
16	6789	Abc	Word	2.9.20	12:00:00	0	Word02092012:00
17	7890	Xyz	Excel	2.9.20	12:00:00	1	Excel02092012:00
18	8901	Abc	Word	2.9.20	12:00:00	0	Word02092012:00
Sheet1

Cell Formulas
Range	Formula
F2:F18	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G18	G2	=C2&TEXT(D2,"ddmmyy")&TEXT(E2,"hh:mm")

 

[arnabmit]

e.g.:

Book1
	A	B	C	D	E	F	G
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID
2	1234	Abc	Word	1.9.20	09:00:00	1	Word01092009:00
3	2345	Abc	Word	1.9.20	09:00:00	0	Word01092009:00
4	3456	Def	PowerPoint	1.9.20	09:00:00	1	PowerPoint01092009:00
5	4567	Abc	Word	1.9.20	12:00:00	1	Word01092012:00
6	5678	Def	PowerPoint	1.9.20	12:00:00	1	PowerPoint01092012:00
7	6789	Xyz	Excel	1.9.20	12:00:00	1	Excel01092012:00
8	7890	Abc	Word	1.9.20	12:00:00	0	Word01092012:00
9	8901	Xyz	Excel	1.9.20	12:00:00	0	Excel01092012:00
10	9012	Def	PowerPoint	1.9.20	12:00:00	0	PowerPoint01092012:00
11	1234	Xyz	Excel	2.9.20	09:00:00	1	Excel02092009:00
12	2345	Def	PowerPoint	2.9.20	12:00:00	1	PowerPoint02092012:00
13	3456	Abc	Word	2.9.20	12:00:00	1	Word02092012:00
14	4567	Xyz	Excel	2.9.20	09:00:00	0	Excel02092009:00
15	5678	Def	PowerPoint	2.9.20	12:00:00	0	PowerPoint02092012:00
16	6789	Abc	Word	2.9.20	12:00:00	0	Word02092012:00
17	7890	Xyz	Excel	2.9.20	12:00:00	1	Excel02092012:00
18	8901	Abc	Word	2.9.20	12:00:00	0	Word02092012:00
Sheet1

Cell Formulas
Range	Formula
F2:F18	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G18	G2	=C2&TEXT(D2,"ddmmyy")&TEXT(E2,"hh:mm")

I wondered the same, however It needs to be in the specified (existing) format as Batch ID is used as a primary key in the data model.

 

[lrobbo314]

It doesn't look like in your example you've shown how your formula fails. It looks like your formula works. Who knows what happens further down the line though.

Anyway, this doesn't get rid of the helper column, but it matches the output and might work where your original formula is failing. To make this work I first sorted the data by Course => then by Date => then by Time, and added the new helper column and ID columns H:I.

Dice.xlsm
	A	B	C	D	E	F	G	H	I	J
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID	Helper2	bID	Check
2	6789	Xyz	Excel	9/1/2020	12:00:00 PM	1	EXC001	1	EXC001	TRUE
3	8901	Xyz	Excel	9/1/2020	12:00:00 PM	0	EXC001	1	EXC001	TRUE
4	1234	Xyz	Excel	9/2/2020	9:00:00 AM	1	EXC002	2	EXC002	TRUE
5	4567	Xyz	Excel	9/2/2020	9:00:00 AM	0	EXC002	2	EXC002	TRUE
6	7890	Xyz	Excel	9/2/2020	12:00:00 PM	1	EXC003	3	EXC003	TRUE
7	9012	Xyz	Excel	9/2/2020	12:00:00 PM	0	EXC003	3	EXC003	TRUE
8	2345	Xyz	Excel	9/3/2020	9:00:00 AM	1	EXC004	4	EXC004	TRUE
9	4567	Xyz	Excel	9/3/2020	9:00:00 AM	0	EXC004	4	EXC004	TRUE
10	5678	Xyz	Excel	9/3/2020	12:00:00 PM	1	EXC005	5	EXC005	TRUE
11	7890	Xyz	Excel	9/3/2020	12:00:00 PM	0	EXC005	5	EXC005	TRUE
12	3456	Def	PowerPoint	9/1/2020	9:00:00 AM	1	POW001	1	POW001	TRUE
13	5678	Def	PowerPoint	9/1/2020	12:00:00 PM	1	POW002	2	POW002	TRUE
14	9012	Def	PowerPoint	9/1/2020	12:00:00 PM	0	POW002	2	POW002	TRUE
15	2345	Def	PowerPoint	9/2/2020	12:00:00 PM	1	POW003	3	POW003	TRUE
16	5678	Def	PowerPoint	9/2/2020	12:00:00 PM	0	POW003	3	POW003	TRUE
17	1234	Def	PowerPoint	9/3/2020	3:00:00 PM	1	POW004	4	POW004	TRUE
18	8901	Def	PowerPoint	9/3/2020	3:00:00 PM	0	POW004	4	POW004	TRUE
19	1234	Abc	Word	9/1/2020	9:00:00 AM	1	WOR001	1	WOR001	TRUE
20	2345	Abc	Word	9/1/2020	9:00:00 AM	0	WOR001	1	WOR001	TRUE
21	4567	Abc	Word	9/1/2020	12:00:00 PM	1	WOR002	2	WOR002	TRUE
22	7890	Abc	Word	9/1/2020	12:00:00 PM	0	WOR002	2	WOR002	TRUE
23	3456	Abc	Word	9/2/2020	12:00:00 PM	1	WOR003	3	WOR003	TRUE
24	6789	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003	3	WOR003	TRUE
25	8901	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003	3	WOR003	TRUE
26	3456	Abc	Word	9/3/2020	3:00:00 PM	1	WOR004	4	WOR004	TRUE
27	6789	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004	4	WOR004	TRUE
28	9012	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004	4	WOR004	TRUE
Sheet3

Cell Formulas
Range	Formula
F2:F28	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G28	G2	=UPPER(LEFT(C2,3))&TEXT(SUMPRODUCT(($C$2:C2=C2)*($F$2:F2=1)),"000")
H2:H28	H2	=IF(C2&D2&E2=C1&D1&E1,H1,IF(C2=C1,H1+1,1))
I2:I28	I2	=UPPER(LEFT(C2,3))&TEXT(H2,"000")
J2:J28	J2	=I2=G2

 

[arnabmit]

It doesn't look like in your example you've shown how your formula fails. It looks like your formula works. Who knows what happens further down the line though.

Anyway, this doesn't get rid of the helper column, but it matches the output and might work where your original formula is failing. To make this work I first sorted the data by Course => then by Date => then by Time, and added the new helper column and ID columns H:I.

Dice.xlsm
	A	B	C	D	E	F	G	H	I	J
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID	Helper2	bID	Check
2	6789	Xyz	Excel	9/1/2020	12:00:00 PM	1	EXC001	1	EXC001	TRUE
3	8901	Xyz	Excel	9/1/2020	12:00:00 PM	0	EXC001	1	EXC001	TRUE
4	1234	Xyz	Excel	9/2/2020	9:00:00 AM	1	EXC002	2	EXC002	TRUE
5	4567	Xyz	Excel	9/2/2020	9:00:00 AM	0	EXC002	2	EXC002	TRUE
6	7890	Xyz	Excel	9/2/2020	12:00:00 PM	1	EXC003	3	EXC003	TRUE
7	9012	Xyz	Excel	9/2/2020	12:00:00 PM	0	EXC003	3	EXC003	TRUE
8	2345	Xyz	Excel	9/3/2020	9:00:00 AM	1	EXC004	4	EXC004	TRUE
9	4567	Xyz	Excel	9/3/2020	9:00:00 AM	0	EXC004	4	EXC004	TRUE
10	5678	Xyz	Excel	9/3/2020	12:00:00 PM	1	EXC005	5	EXC005	TRUE
11	7890	Xyz	Excel	9/3/2020	12:00:00 PM	0	EXC005	5	EXC005	TRUE
12	3456	Def	PowerPoint	9/1/2020	9:00:00 AM	1	POW001	1	POW001	TRUE
13	5678	Def	PowerPoint	9/1/2020	12:00:00 PM	1	POW002	2	POW002	TRUE
14	9012	Def	PowerPoint	9/1/2020	12:00:00 PM	0	POW002	2	POW002	TRUE
15	2345	Def	PowerPoint	9/2/2020	12:00:00 PM	1	POW003	3	POW003	TRUE
16	5678	Def	PowerPoint	9/2/2020	12:00:00 PM	0	POW003	3	POW003	TRUE
17	1234	Def	PowerPoint	9/3/2020	3:00:00 PM	1	POW004	4	POW004	TRUE
18	8901	Def	PowerPoint	9/3/2020	3:00:00 PM	0	POW004	4	POW004	TRUE
19	1234	Abc	Word	9/1/2020	9:00:00 AM	1	WOR001	1	WOR001	TRUE
20	2345	Abc	Word	9/1/2020	9:00:00 AM	0	WOR001	1	WOR001	TRUE
21	4567	Abc	Word	9/1/2020	12:00:00 PM	1	WOR002	2	WOR002	TRUE
22	7890	Abc	Word	9/1/2020	12:00:00 PM	0	WOR002	2	WOR002	TRUE
23	3456	Abc	Word	9/2/2020	12:00:00 PM	1	WOR003	3	WOR003	TRUE
24	6789	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003	3	WOR003	TRUE
25	8901	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003	3	WOR003	TRUE
26	3456	Abc	Word	9/3/2020	3:00:00 PM	1	WOR004	4	WOR004	TRUE
27	6789	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004	4	WOR004	TRUE
28	9012	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004	4	WOR004	TRUE
Sheet3

Cell Formulas
Range	Formula
F2:F28	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G28	G2	=UPPER(LEFT(C2,3))&TEXT(SUMPRODUCT(($C$2:C2=C2)*($F$2:F2=1)),"000")
H2:H28	H2	=IF(C2&D2&E2=C1&D1&E1,H1,IF(C2=C1,H1+1,1))
I2:I28	I2	=UPPER(LEFT(C2,3))&TEXT(H2,"000")
J2:J28	J2	=I2=G2

Thanks! will try it, though I might not be allowed to sort the data though. Could you think of any alternate way to generate the IDs?

 

[lrobbo314]

I don't understand this task. Is this for work? Why would you not be able to sort data...?

Anyway, you said no VBA. But, you also said you have Excel 2016. Which means you have Power Query.

Now if, for some reason I can't imagine, you can't sort the data, I really wonder about the probability of being able to use Power Query... But hey, here it goes anyway.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID		StudentID	Teacher	Course	Date	Time	BatchID
2	1234	Abc	Word	9/1/2020	9:00:00 AM	1	WOR001		1234	Abc	Word	9/1/2020	9:00:00 AM	WOR001
3	2345	Abc	Word	9/1/2020	9:00:00 AM	0	WOR001		2345	Abc	Word	9/1/2020	9:00:00 AM	WOR001
4	3456	Def	PowerPoint	9/1/2020	9:00:00 AM	1	POW001		3456	Def	PowerPoint	9/1/2020	9:00:00 AM	POW001
5	4567	Abc	Word	9/1/2020	12:00:00 PM	1	WOR002		4567	Abc	Word	9/1/2020	12:00:00 PM	WOR002
6	5678	Def	PowerPoint	9/1/2020	12:00:00 PM	1	POW002		5678	Def	PowerPoint	9/1/2020	12:00:00 PM	POW002
7	6789	Xyz	Excel	9/1/2020	12:00:00 PM	1	EXC001		6789	Xyz	Excel	9/1/2020	12:00:00 PM	EXC001
8	7890	Abc	Word	9/1/2020	12:00:00 PM	0	WOR002		7890	Abc	Word	9/1/2020	12:00:00 PM	WOR002
9	8901	Xyz	Excel	9/1/2020	12:00:00 PM	0	EXC001		8901	Xyz	Excel	9/1/2020	12:00:00 PM	EXC001
10	9012	Def	PowerPoint	9/1/2020	12:00:00 PM	0	POW002		9012	Def	PowerPoint	9/1/2020	12:00:00 PM	POW002
11	1234	Xyz	Excel	9/2/2020	9:00:00 AM	1	EXC002		1234	Xyz	Excel	9/2/2020	9:00:00 AM	EXC002
12	2345	Def	PowerPoint	9/2/2020	12:00:00 PM	1	POW003		2345	Def	PowerPoint	9/2/2020	12:00:00 PM	POW003
13	3456	Abc	Word	9/2/2020	12:00:00 PM	1	WOR003		3456	Abc	Word	9/2/2020	12:00:00 PM	WOR003
14	4567	Xyz	Excel	9/2/2020	9:00:00 AM	0	EXC002		4567	Xyz	Excel	9/2/2020	9:00:00 AM	EXC002
15	5678	Def	PowerPoint	9/2/2020	12:00:00 PM	0	POW003		5678	Def	PowerPoint	9/2/2020	12:00:00 PM	POW003
16	6789	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003		6789	Abc	Word	9/2/2020	12:00:00 PM	WOR003
17	7890	Xyz	Excel	9/2/2020	12:00:00 PM	1	EXC003		7890	Xyz	Excel	9/2/2020	12:00:00 PM	EXC003
18	8901	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003		8901	Abc	Word	9/2/2020	12:00:00 PM	WOR003
19	9012	Xyz	Excel	9/2/2020	12:00:00 PM	0	EXC003		9012	Xyz	Excel	9/2/2020	12:00:00 PM	EXC003
20	1234	Def	PowerPoint	9/3/2020	3:00:00 PM	1	POW004		1234	Def	PowerPoint	9/3/2020	3:00:00 PM	POW004
21	2345	Xyz	Excel	9/3/2020	9:00:00 AM	1	EXC004		2345	Xyz	Excel	9/3/2020	9:00:00 AM	EXC004
22	3456	Abc	Word	9/3/2020	3:00:00 PM	1	WOR004		3456	Abc	Word	9/3/2020	3:00:00 PM	WOR004
23	4567	Xyz	Excel	9/3/2020	9:00:00 AM	0	EXC004		4567	Xyz	Excel	9/3/2020	9:00:00 AM	EXC004
24	5678	Xyz	Excel	9/3/2020	12:00:00 PM	1	EXC005		5678	Xyz	Excel	9/3/2020	12:00:00 PM	EXC005
25	6789	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004		6789	Abc	Word	9/3/2020	3:00:00 PM	WOR004
26	7890	Xyz	Excel	9/3/2020	12:00:00 PM	0	EXC005		7890	Xyz	Excel	9/3/2020	12:00:00 PM	EXC005
27	8901	Def	PowerPoint	9/3/2020	3:00:00 PM	0	POW004		8901	Def	PowerPoint	9/3/2020	3:00:00 PM	POW004
28	9012	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004		9012	Abc	Word	9/3/2020	3:00:00 PM	WOR004
Sheet1

Cell Formulas
Range	Formula
F2:F28	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G28	G2	=UPPER(LEFT(C2,3))&TEXT(SUMPRODUCT(($C$2:C2=C2)*($F$2:F2=1)),"000")

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ID = Table.AddIndexColumn(Source, "IDX", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(ID, {"Course", "Date", "Time"}, {{"Count", each _, type table [StudentID=nullable number, Teacher=nullable text, Course=nullable text, Date=nullable date, Time=nullable time]}}),
    Index = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    RunningCount = Table.AddColumn(Index, "Custom", (OT) => Table.RowCount(Table.SelectRows(Index, (IT)=> IT[Index] <= OT[Index] and IT[Course] = OT[Course]))),
    BatchID = Table.AddColumn(RunningCount, "Custom.1", each Text.Upper(Text.Start([Course],3))&Text.PadStart(Text.From([Custom]),3,"0")),
    Expand = Table.ExpandTableColumn(BatchID, "Count", {"StudentID", "Teacher", "Course", "Date", "Time", "IDX"}, {"Count.StudentID", "Count.Teacher", "Count.Course", "Count.Date", "Count.Time","Count.IDX"}),
    Types = Table.TransformColumnTypes(Expand,{{"Date", type date}, {"Time", type time}, {"Count.StudentID", Int64.Type}, {"Count.IDX", Int64.Type}, {"Index", Int64.Type}, {"Custom", Int64.Type}, {"Custom.1", type text}, {"Count.Date", Int64.Type}, {"Count.Time", type number}}),
    Sort = Table.Sort(Types,{{"Count.IDX", Order.Ascending}}),
    SC = Table.SelectColumns(Sort,{"Count.StudentID", "Count.Teacher", "Count.Course", "Count.Date", "Count.Time", "Custom.1"}),
    RC = Table.RenameColumns(SC,{{"Custom.1", "BatchID"}, {"Count.StudentID", "StudentID"}, {"Count.Teacher", "Teacher"}, {"Count.Course", "Course"}, {"Count.Date", "Date"}, {"Count.Time", "Time"}})
in
    RC

 

[arnabmit]

I don't understand this task. Is this for work? Why would you not be able to sort data...?

Anyway, you said no VBA. But, you also said you have Excel 2016. Which means you have Power Query.

Now if, for some reason I can't imagine, you can't sort the data, I really wonder about the probability of being able to use Power Query... But hey, here it goes anyway.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	StudentID	Teacher	Course	Date	Time	Helper	Batch ID		StudentID	Teacher	Course	Date	Time	BatchID
2	1234	Abc	Word	9/1/2020	9:00:00 AM	1	WOR001		1234	Abc	Word	9/1/2020	9:00:00 AM	WOR001
3	2345	Abc	Word	9/1/2020	9:00:00 AM	0	WOR001		2345	Abc	Word	9/1/2020	9:00:00 AM	WOR001
4	3456	Def	PowerPoint	9/1/2020	9:00:00 AM	1	POW001		3456	Def	PowerPoint	9/1/2020	9:00:00 AM	POW001
5	4567	Abc	Word	9/1/2020	12:00:00 PM	1	WOR002		4567	Abc	Word	9/1/2020	12:00:00 PM	WOR002
6	5678	Def	PowerPoint	9/1/2020	12:00:00 PM	1	POW002		5678	Def	PowerPoint	9/1/2020	12:00:00 PM	POW002
7	6789	Xyz	Excel	9/1/2020	12:00:00 PM	1	EXC001		6789	Xyz	Excel	9/1/2020	12:00:00 PM	EXC001
8	7890	Abc	Word	9/1/2020	12:00:00 PM	0	WOR002		7890	Abc	Word	9/1/2020	12:00:00 PM	WOR002
9	8901	Xyz	Excel	9/1/2020	12:00:00 PM	0	EXC001		8901	Xyz	Excel	9/1/2020	12:00:00 PM	EXC001
10	9012	Def	PowerPoint	9/1/2020	12:00:00 PM	0	POW002		9012	Def	PowerPoint	9/1/2020	12:00:00 PM	POW002
11	1234	Xyz	Excel	9/2/2020	9:00:00 AM	1	EXC002		1234	Xyz	Excel	9/2/2020	9:00:00 AM	EXC002
12	2345	Def	PowerPoint	9/2/2020	12:00:00 PM	1	POW003		2345	Def	PowerPoint	9/2/2020	12:00:00 PM	POW003
13	3456	Abc	Word	9/2/2020	12:00:00 PM	1	WOR003		3456	Abc	Word	9/2/2020	12:00:00 PM	WOR003
14	4567	Xyz	Excel	9/2/2020	9:00:00 AM	0	EXC002		4567	Xyz	Excel	9/2/2020	9:00:00 AM	EXC002
15	5678	Def	PowerPoint	9/2/2020	12:00:00 PM	0	POW003		5678	Def	PowerPoint	9/2/2020	12:00:00 PM	POW003
16	6789	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003		6789	Abc	Word	9/2/2020	12:00:00 PM	WOR003
17	7890	Xyz	Excel	9/2/2020	12:00:00 PM	1	EXC003		7890	Xyz	Excel	9/2/2020	12:00:00 PM	EXC003
18	8901	Abc	Word	9/2/2020	12:00:00 PM	0	WOR003		8901	Abc	Word	9/2/2020	12:00:00 PM	WOR003
19	9012	Xyz	Excel	9/2/2020	12:00:00 PM	0	EXC003		9012	Xyz	Excel	9/2/2020	12:00:00 PM	EXC003
20	1234	Def	PowerPoint	9/3/2020	3:00:00 PM	1	POW004		1234	Def	PowerPoint	9/3/2020	3:00:00 PM	POW004
21	2345	Xyz	Excel	9/3/2020	9:00:00 AM	1	EXC004		2345	Xyz	Excel	9/3/2020	9:00:00 AM	EXC004
22	3456	Abc	Word	9/3/2020	3:00:00 PM	1	WOR004		3456	Abc	Word	9/3/2020	3:00:00 PM	WOR004
23	4567	Xyz	Excel	9/3/2020	9:00:00 AM	0	EXC004		4567	Xyz	Excel	9/3/2020	9:00:00 AM	EXC004
24	5678	Xyz	Excel	9/3/2020	12:00:00 PM	1	EXC005		5678	Xyz	Excel	9/3/2020	12:00:00 PM	EXC005
25	6789	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004		6789	Abc	Word	9/3/2020	3:00:00 PM	WOR004
26	7890	Xyz	Excel	9/3/2020	12:00:00 PM	0	EXC005		7890	Xyz	Excel	9/3/2020	12:00:00 PM	EXC005
27	8901	Def	PowerPoint	9/3/2020	3:00:00 PM	0	POW004		8901	Def	PowerPoint	9/3/2020	3:00:00 PM	POW004
28	9012	Abc	Word	9/3/2020	3:00:00 PM	0	WOR004		9012	Abc	Word	9/3/2020	3:00:00 PM	WOR004
Sheet1

Cell Formulas
Range	Formula
F2:F28	F2	=IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=D2)*($E$2:E2=E2))>1,0,1)
G2:G28	G2	=UPPER(LEFT(C2,3))&TEXT(SUMPRODUCT(($C$2:C2=C2)*($F$2:F2=1)),"000")

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ID = Table.AddIndexColumn(Source, "IDX", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(ID, {"Course", "Date", "Time"}, {{"Count", each _, type table [StudentID=nullable number, Teacher=nullable text, Course=nullable text, Date=nullable date, Time=nullable time]}}),
    Index = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    RunningCount = Table.AddColumn(Index, "Custom", (OT) => Table.RowCount(Table.SelectRows(Index, (IT)=> IT[Index] <= OT[Index] and IT[Course] = OT[Course]))),
    BatchID = Table.AddColumn(RunningCount, "Custom.1", each Text.Upper(Text.Start([Course],3))&Text.PadStart(Text.From([Custom]),3,"0")),
    Expand = Table.ExpandTableColumn(BatchID, "Count", {"StudentID", "Teacher", "Course", "Date", "Time", "IDX"}, {"Count.StudentID", "Count.Teacher", "Count.Course", "Count.Date", "Count.Time","Count.IDX"}),
    Types = Table.TransformColumnTypes(Expand,{{"Date", type date}, {"Time", type time}, {"Count.StudentID", Int64.Type}, {"Count.IDX", Int64.Type}, {"Index", Int64.Type}, {"Custom", Int64.Type}, {"Custom.1", type text}, {"Count.Date", Int64.Type}, {"Count.Time", type number}}),
    Sort = Table.Sort(Types,{{"Count.IDX", Order.Ascending}}),
    SC = Table.SelectColumns(Sort,{"Count.StudentID", "Count.Teacher", "Count.Course", "Count.Date", "Count.Time", "Custom.1"}),
    RC = Table.RenameColumns(SC,{{"Custom.1", "BatchID"}, {"Count.StudentID", "StudentID"}, {"Count.Teacher", "Teacher"}, {"Count.Course", "Course"}, {"Count.Date", "Date"}, {"Count.Time", "Time"}})
in
    RC

I inherited legacy database, and trying to make it efficient. Sheet contains 200,000 plus rows with 30+ columns. there are at least 4 columns with sumproduct formula to calculate conditional unique numbers. A sort triggers all the formulas to recalculate, which freezes excel for 3-4 hours.

Will it be possible to guide me on how to use the power query? I know how to import tables as query, but don't know how or where to put in the code.

 

[lrobbo314]

I copied your data from post #1 using the XL2BB copy icon and pasted it into my workbook. Then I select any cell in the range I just pasted. Then, hit Alt+A+T+P. This adds the data to PQ. Then, in the PQ editor, under the 'Home' tab, I think, you click 'Advanced Editor' and paste the M code I posted before. Then close and load and it should be good to go. I did have to fiddle with the data types for the date and time fields, but it should work regardless.

 

[arnabmit]

I copied your data from post #1 using the XL2BB copy icon and pasted it into my workbook. Then I select any cell in the range I just pasted. Then, hit Alt+A+T+P. This adds the data to PQ. Then, in the PQ editor, under the 'Home' tab, I think, you click 'Advanced Editor' and paste the M code I posted before. Then close and load and it should be good to go. I did have to fiddle with the data types for the date and time fields, but it should work regardless.

Great! I will try this.

Meanwhile, can I do something like this in a calculated column using DAX, instead of M code? This is so that I can modify it later if needed if parameters change.