Concat Duplicate Data

[spyldbrat]

In col K I have list of locations. In column G, I have list of dates. If column D contains the words "Store-00989" and column G has the same date listed on another row, that shares the specific words in d, then in col E, I want to concat the data to show the location name in G and the words "Shift 1" (Example: Store-00989 Shift 1). If another row contains the same duplicate information, then I would like the words "Shift 1" to read "Shift 2" and so on. If there are no duplicates between D & G that do not meet this criteria, then the result should be location name listed in G.

I am not sure if it is possible to change the # after shift, but I need to make each of these duplicate shifts unique.

 

[kweaver]

I almost follow this. Would help of you'd post a few examples of the data and what you want returned (and use XL2BB, please).

 

[spyldbrat]

Unfortunately, my company has blocked me downloading add-in's. I hope the screen shot below helps.

Store-00989 shares the date of 9/13 on rows 2, 3 and 7. In Column E, the value should be "Store-00989 Unarmed 1", "Store-00989 Unarmed 2", "Store-00989 Unarmed 3" respectively
Store-00989 shares the date of 9/15 on rows 6 and 10. In Colum E, the value returned should be "Store-00989 Unarmed 1", "Store-00989 Unarmed 2" respectively
Loc-1971 shares the date of 9/16 on rows 8 and 9. In Column E, the value returned is Loc-1971 since it does not contain the specific words "Store-00989" in Col D
As with Loc-1971, lines rows 4 &5 would return the value from Col D.

 

[mrshl9898]

=IF(COUNTIFS(D:D,D2,G:G,G2)>1,D2&" Unarmed "&COUNTIFS(D$2:D2,D2,G$2:G2,G2),D2)

 

[spyldbrat]

MrshI9898, I am getting the message below:

 

[mrshl9898]

Sorry, try again, I updated the formula

 

[spyldbrat]

The result on rows 8 and 9 should be "LOC-1971" since they do not contain the words "STORE-00989". Other than that it works!

 

[spyldbrat]

Actually, I just figured it out =IF(COUNTIFS(D:D,"Store-00989",G:G,G2)>1,D2&" Unarmed "&COUNTIFS(D$2:D2,D2,G$2:G2,G2),D2)

But what if I wanted to add two conditions? For example, lets say I only want it to add unarmed 1, 2, etc if D contains "Store-00989" AND "Loc-1971"?

 

[kweaver]

If "store" is the key to incrementing the Unarmed, then maybe mrshl9898's nice formula could be altered to:

=IF(AND("Store"=LEFT(D2,5),COUNTIFS(D:D,D2,G:G,G2)>1),D2&" Unarmed "&COUNTIFS(D$2:D2,D2,G$2:G2,G2),D2)

Book4
	D	E	F	G
2	Store-00989	Store-00989 Unarmed 1		09/13/20
3	Store-00989	Store-00989 Unarmed 2		09/13/20
4	BL-000154	BL-000154		09/14/20
5	BL-000182	BL-000182		09/14/20
6	Store-00989	Store-00989 Unarmed 1		09/15/20
7	Store-00989	Store-00989 Unarmed 3		09/13/20
8	Loc-1971	Loc-1971		09/16/20
9	Loc-1971	Loc-1971		09/16/20
10	Store-00989	Store-00989 Unarmed 2		09/15/20
11	Store-11111	Store-11111 Unarmed 1		9/15/2020
12	Store-11111	Store-11111 Unarmed 2		9/15/2020
spyldbrat

Cell Formulas
Range	Formula
E2:E12	E2	=IF(AND("Store"=LEFT(D2,5),COUNTIFS(D:D,D2,G:G,G2)>1),D2&" Unarmed "&COUNTIFS(D$2:D2,D2,G$2:G2,G2),D2)

 

[mrshl9898]

You could use an OR in your IF

=IF(OR(COUNTIFS(D:D,"Store-00989",G:G,G2)>1,COUNTIFS(D:D,"Loc-1971",G:G,G2)>1),D2&" Unarmed "&COUNTIFS(D$2:D2,D2,G$2:G2,G2),D2)