Formula help needed to create a unique ID

[Dmat]

Hi all. I'm having trouble figuring out what formula would work best to create my unique tagging.

If the GL Acct starts with a 4 then I want to tag all of the Documents # associated it with Yes. I'm able to write an if statement to tag the GL Acct with a 4 but can't figure out how to tag the Document #s that are the same.

Thanks in advance. Any help would be appreciated.

 

[6StringJazzer]

When asking this type of question it helps to paste in your actual data instead of an image, because many people won't want to type all of that in for testing. We also can't see your column letters or row numbers, or whether this is a structured table or just a range, and whether your values are numeric or text (they are left-justified so that suggests text).

If this is a range starting in A1 and the GL Acct values are text, put this in A2 and copy down.

=IF(COUNTIFS(A:A,A2,B:B,"4*")>0,"Yes","No")

If any of those assumptions are wrong, please provide more detail and the actual data.

 

[Dmat]

Apologies. I've copied and pasted a portion of the file. The countif formula above didn't work, as I also tried =left(GL Acct,1) to isolate the 4xxxxx accounts.

Document	GL Acct	Amount	Unique Tagging
0100613611	110130	137.56	No
0100613611	220111	(31.95)	No
0100613611	225301	(12.51)	No
0100613611	260110	(93.10)	No
0100614099	260110	93.10	Yes
0100614099	406900	(93.10)	Yes
0100617011	110130	137.56	No
0100617011	220111	(31.95)	No
0100617011	225301	(12.51)	No
0100617011	260110	(93.10)	No
0100617498	260110	93.10	Yes
0100617498	406900	(93.10)	Yes

 

[6StringJazzer]

Is the data in the second column numeric or text? As I mentioned, normally when you see data left-justified it's text, but you can format numeric to the left also.

What version of Excel are you using? It helps to put that in your user profile because I don't want to give a solution for MS 365 that won't work if you have 2019.

 

[6StringJazzer]

Are all GL account numbers always 6 digits?

 

[6StringJazzer]

If GL account numbers are numeric, and always 6 digits, you can use this:

$scratch.xlsm
	A	B	C	D	E
1	Document	GL Acct	Amount	Unique Tagging	Unique Tagging
2	0100613611	110130	137.56	No	No
3	0100613611	220111	-31.95	No	No
4	0100613611	225301	-12.51	No	No
5	0100613611	260110	-93.1	No	No
6	0100614099	260110	93.1	Yes	Yes
7	0100614099	406900	-93.1	Yes	Yes
8	0100617011	110130	137.56	No	No
9	0100617011	220111	-31.95	No	No
10	0100617011	225301	-12.51	No	No
11	0100617011	260110	-93.1	No	No
12	0100617498	260110	93.1	Yes	Yes
13	0100617498	406900	-93.1	Yes	Yes
tagging

Cell Formulas
Range	Formula
E2:E13	E2	=IF(COUNTIFS(A:A,A2,B:B,">=400000",B:B,"<500000")>0,"Yes","No")

 

[Dmat]

I'm working on MS 365 and all the numbers on the table are text.

 

[6StringJazzer]

In that case my original COUNTIFS formula works. Double check how you entered it. This version uses text in the first two columns.

$scratch.xlsm
	A	B	C	D	E
1	Document	GL Acct	Amount	Unique Tagging	Unique Tagging
2	0100613611	110130	137.56	No	No
3	0100613611	220111	-31.95	No	No
4	0100613611	225301	-12.51	No	No
5	0100613611	260110	-93.1	No	No
6	0100614099	260110	93.1	Yes	Yes
7	0100614099	406900	-93.1	Yes	Yes
8	0100617011	110130	137.56	No	No
9	0100617011	220111	-31.95	No	No
10	0100617011	225301	-12.51	No	No
11	0100617011	260110	-93.1	No	No
12	0100617498	260110	93.1	Yes	Yes
13	0100617498	406900	-93.1	Yes	Yes
tagging (2)

Cell Formulas
Range	Formula
E2:E13	E2	=IF(COUNTIFS(A:A,A2,B:B,"4*")>0,"Yes","No")

 

[Dmat]

Thank you 6StringJazzer. The last formula worked with Text values. I really appreciate your help and it will save me so much time.

Out of curiosity, what does the * do in formula?

 

[6StringJazzer]

It's a wildcard indicator that means "match any number of characters". So "4*" means "match any cell that starts with 4 followed by any characters."