# Making a list of duplicates by searching 6 columns and using 2 criteria

#### Zacariah171

##### New Member
I have been able to make a list in column CY that lists any duplicates found in columns D, J, P, and V. Any duplicates found in those columns are listed once in CY. I have the formula written for that listed below and it works fine. Now, I'd like to alter that formula so that it will just list any duplicates that have "PRI" in the column next to it: E, K, Q, and W. I just cant seem to figure out how to add in that extra criteria. In the attached picture, the only item that should be listed in column CY should be Unit 56. Unit 11 is listed twice in column D but only one of those has a Status of PRI next to it, so it's not a duplicate that I'm looking for and should not be listed in CY. Unit 56 is listed twice; once in column D and once in column J and both have a Status of PRI, so that is a duplicate that I'm looking for, so it should be listed once in CY7. Any help is greatly appreciated and please let me know if you need more information.

This is the formula I'm looking to alter to accommodate the extra criteria.
=IFNA(IFERROR(IFERROR(IFERROR(
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$D\$7:\$D\$37)=0)*(COUNTIF(\$D\$7:\$D\$37, \$D\$7:\$D\$37)>1)), \$D\$7:\$D\$37),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$J\$7:\$J\$37)=0)*((COUNTIF(\$J\$7:\$J\$37, \$J\$7:\$J\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$J\$7:\$J\$37)>0))),\$J\$7:\$J\$37)),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$P\$7:\$P\$37)=0)*((COUNTIF(\$P\$7:\$P\$37, \$P\$7:\$P\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$P\$7:\$P\$37)>0)+(COUNTIF(\$J\$7:\$J\$37, \$P\$7:\$P\$37)>0))), \$P\$7:\$P\$37)),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$V\$7:\$V\$37)=0)*((COUNTIF(\$V\$7:\$V\$37, \$V\$7:\$V\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$V\$7:\$V\$37)>0)+(COUNTIF(\$J\$7:\$J\$37, \$V\$7:\$V\$37)>0)+(COUNTIF(\$P\$7:\$P\$37, \$V\$7:\$V\$37)>0))), \$V\$7:\$V\$37)), "")

Thank you.

#### Attachments

• Usage.PNG
67.5 KB · Views: 4

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Zacariah171

##### New Member
Thanks for the reply. Ok. First time uploading a mini-sheet. Please let me know if there's anything else you need with it. In the example on the sheet, Unit 11 is listed twice in column D but it's not a duplicate that I'm looking for because only one of them has the Status "PRI", so it should not be showing up in my list in the CY column. Unit 56 is listed twice, once in column D and once in column J, and both have the Status "PRI", so that Unit 56 should show up in my CY column. In the CZ column next to it, "1st Box" should be 75 (From column A), and DA "2nd Box" should be 71 (From column A). Unit 22 is listed across the table 5 times but only 3 have the "PRI" Status, so it should be listed in CY and in column CZ next to in the "1st Box" should be 101, "2nd Box" should be 101, and "3rd Box" should be 108. I hope that provides enough detail of what I'm trying to accomplish.

Datalog Monthly Usage_11.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
6IDReceivedReturnedUnit 1Status 1Start 1End 1Days 1Location 1Unit 2Status 2Start 2End 2Days 2Location 2Unit 3Status 3Start 3End 3Days 3Location 3Unit 4Status 4
7074Unit 11PRI1/31/2321Oklahoma
8071Unit 11BU OklahomaUnit 56PRI1/221/287Oklahoma
9075Unit 56PRI1/41/2118Oklahoma
10101Unit 22PRI1/21/1514TexasUnit 22PRI1/181/236Texas
11106Unit 22BU TexasUnit 22BU Texas
12108  Unit 22PRI1/241/318
13136
14138
15151
16159
17185
18189
19190
20301
21302
22307
23385
24481
25802
26839
27811
28814
29816
30818
31824
32830
33832
34833
35838
36854
37856
Jan 2021
Cell Formulas
RangeFormula
H7:H37H7=IF(AND([@[Start 1]]<>"", [@[End 1]]<>""), DAYS([@[End 1]],[@[Start 1]])+1, 0)
N7:N37N7=IF(AND([@[Start 2]]<>"", [@[End 2]]<>""), DAYS([@[End 2]],[@[Start 2]])+1, 0)
T7:T37T7=IF(AND([@[Start 3]]<>"", [@[End 3]]<>""), DAYS([@[End 3]],[@[Start 3]])+1, 0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H36,Z36,T36,N36Cell Value=0textNO
H35,Z35,T35,N35Cell Value=0textNO
H34,Z34,T34,N34Cell Value=0textNO
H33,Z33,T33,N33Cell Value=0textNO
H32,Z32,T32,N32Cell Value=0textNO
H31,Z31,T31,N31Cell Value=0textNO
H37:H39,Z37:Z39,T37:T39,N37:N39,N41:N1048576,T41:T1048576,Z41:Z1048576,H41:H1048576Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
D7:D37List='Data Validation Table'!\$A\$3:\$A\$102
E7:E37List='Data Validation Table'!\$C\$3:\$C\$5
I7:I37List='Data Validation Table'!\$B\$3:\$B\$11
J7:J37List='Data Validation Table'!\$A\$3:\$A\$102
K7:K37List='Data Validation Table'!\$C\$3:\$C\$5
O7:O37List='Data Validation Table'!\$B\$3:\$B\$11
P7:P37List='Data Validation Table'!\$A\$3:\$A\$102
Q7:Q37List='Data Validation Table'!\$C\$3:\$C\$5
U7:U37List='Data Validation Table'!\$B\$3:\$B\$11
V7:V37List='Data Validation Table'!\$A\$3:\$A\$102
W7:W37List='Data Validation Table'!\$C\$3:\$C\$5

#### Zacariah171

##### New Member
I couldn't load everything I needed to in the first mini-sheet and when I try to load another mini **** for the rest, which isn't too much, I get a message saying that I've exceeded my limit. So, here is a screenshot of the results table and a sample of the formulas used. I hope this helps.

Formula in CY7:
=IFNA(IFERROR(IFERROR(IFERROR(
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$D\$7:\$D\$37)=0)*(COUNTIF(\$D\$7:\$D\$37, \$D\$7:\$D\$37)>1)), \$D\$7:\$D\$37),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$J\$7:\$J\$37)=0)*((COUNTIF(\$J\$7:\$J\$37, \$J\$7:\$J\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$J\$7:\$J\$37)>0))),\$J\$7:\$J\$37)),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$P\$7:\$P\$37)=0)*((COUNTIF(\$P\$7:\$P\$37, \$P\$7:\$P\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$P\$7:\$P\$37)>0)+(COUNTIF(\$J\$7:\$J\$37, \$P\$7:\$P\$37)>0))), \$P\$7:\$P\$37)),
LOOKUP(2, 1/((COUNTIF(\$CY\$6:CY6, \$V\$7:\$V\$37)=0)*((COUNTIF(\$V\$7:\$V\$37, \$V\$7:\$V\$37)>1)+(COUNTIF(\$D\$7:\$D\$37, \$V\$7:\$V\$37)>0)+(COUNTIF(\$J\$7:\$J\$37, \$V\$7:\$V\$37)>0)+(COUNTIF(\$P\$7:\$P\$37, \$V\$7:\$V\$37)>0))), \$V\$7:\$V\$37)), "")

Formula in CZ7
=IF(\$CY7="", "", IFERROR(INDEX(\$A\$7:\$A\$37,SMALL(IF(\$D\$7:\$D\$37:\$J\$7:\$J\$37:\$P\$7:\$P\$37:\$V\$7:\$V\$37=\$CY7,ROW(\$A\$7:\$A\$37)-MIN(ROW(\$A\$7:\$A\$37))+1),COLUMNS(\$CY7:CY7))),""))

Formula in DA7
=IF(\$CY7="", "", IFERROR(INDEX(\$A\$7:\$A\$37,SMALL(IF(\$D\$7:\$D\$37:\$J\$7:\$J\$37:\$P\$7:\$P\$37:\$V\$7:\$V\$37=\$CY7,ROW(\$A\$7:\$A\$37)-MIN(ROW(\$A\$7:\$A\$37))+1),COLUMNS(\$CY7:CZ7))),""))

#### Attachments

• 1611759012338.png
11.9 KB · Views: 0

##### Well-known Member

I understand What exactly You want, But with your Table Structure , it is very Hard & long Formula.
You Should Change Your Table Structure To this to Search Units At One Column & Status At One Column.

This is Example:
TEST.xlsx
ABCDEFGHIJKLMNOPQRST
274Unit 111PRI441994421921Oklahoma1Unit 56PRI7571
371Unit 111BU0Oklahoma1Unit 22PRI101101108
475Unit 561PRI442004421718Oklahoma1Unit 22BU106106
5101Unit 221PRI441984421114Texas1
6106Unit 221BU0Texas1
774200
871Unit 562PRI44218442247Oklahoma2
975200
10101Unit 222PRI44214442196Texas2
11106Unit 222BU0Texas2
1274300
1371300
1475300
15101300
16106300
17108Unit 223PRI442204422783
18
Sheet2
Cell Formulas
RangeFormula
N2:N17N2=IFERROR(INDEX(Table2[Unit],SMALL(IF((\$L\$2:\$L\$17=2), ROW(Table2[Unit])-ROW(\$D\$2)+1),ROWS(\$N\$1:N1))),"")
O2:O17O2=IFERROR(INDEX(Table2[Status],SMALL(IF((\$L\$2:\$L\$17=2), ROW(Table2[Status])-ROW(\$F\$2)+1),ROWS(\$O\$1:O1))),"")
P2:S17P2=IFERROR(IF(N2="","",INDEX(Table2[ID],SMALL(IF((Table2[Unit]=\$N2)*(Table2[Status]=\$O2), ROW(Table2[Days])-ROW(\$I\$2)+1),COLUMNS(\$P\$1:P1)))),"")
I2:I17I2=IF(AND([@Start]<>"", [@End]<>""), DAYS([@End],[@Start])+1, 0)
L2:L17L2=COUNTIFS(\$D\$2:D2,D2,\$F\$2:F2,F2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

#### Zacariah171

##### New Member
Unfortunately, restructuring the table like that wont work because it would change way too many other things in my sheet. Thank you for your time looking into it though.

##### Well-known Member

What about Create New Table Structure at new Sheet with macro (VBA)?
And then Do calculation on it and finally Paste Results at range you want ( we can do all of this with Macro)

##### Well-known Member
If its OK, I add Transported Data At Sheet2 and then Extract what you want and Paste Back at Sheet1
VBA Code:
``````Option Explicit

Sub TransformData2()
Dim i As Long, j As Long, Sh1 As Worksheet, Sh2 As Worksheet, Lr As Long, Lr1 As Long, Lr2 As Long
Dim K As Long, L As Long, M As Long, N As Long, P As Long, Lr3 As Long
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
For i = Lr To 7 Step -1
If Sh1.Cells(i, 4).Value <> "" Then
K = i
GoTo Res2
End If
Next i
Res2:
Lr1 = 7
Lr2 = K
Sh2.Range("A6:C6").Value = Sh1.Range("A6:C6").Value
For j = 4 To 9
Sh2.Cells(6, j).Value = Left(Sh1.Cells(6, j).Value, Len(Sh1.Cells(6, j).Value) - 1)
Next j
Sh2.Range("J6").Value = "Unit Type"
Sh2.Range("K6").Value = "CountIFS"
For j = 4 To 26 Step 6
For i = 7 To K
If Sh1.Cells(i, j).Value <> "" Then
M = Lr2 - (Lr1 + K) + i + 7
N = Lr1 + i - (7 + P)
Sh2.Range("A" & N & ":C" & N).Value = Sh1.Range("A" & M & ":C" & M).Value
Range(Sh1.Cells(M, j), Sh1.Cells(M, j + 5)).Copy Sh2.Cells(N, 4)
Sh2.Range("J" & N).Value = "Unit" & (Int(j / 6) + 1)
Sh2.Range("K" & N).Value = Application.WorksheetFunction.CountIfs(Sh2.Range("D2:D" & N), Sh2.Range("D" & N), Sh2.Range("E2:E" & N), Sh2.Range("E" & N))
Else
P = P + 1
End If
Next i
For i = Lr To 2 Step -1
If Sh1.Cells(i, j + 6).Value <> "" Then
K = i
GoTo Res3
End If
Next i
Res3:
Lr1 = Lr2 + 1
Lr2 = Lr2 + K - 6
Next j

With Sh2
Lr2 = .Cells(Rows.Count, 11).End(xlUp).Row
P = 7
.Range("M6").Value = "Unit"
.Range("N6").Value = "Status"
For i = 7 To Lr2
If Sh2.Cells(i, 11).Value = 2 Then
.Range("M" & P).Value = .Range("D" & i).Value
.Range("N" & P).Value = .Range("E" & i).Value
P = P + 1
End If
Next i
Lr3 = .Cells(Rows.Count, 11).End(xlUp).Row
For j = 15 To 19
Sh2.Cells(6, j).Value = j - 14
Next j

For i = 7 To Lr3
j = 15
For L = 7 To Lr2
If .Range("M" & i).Value = .Range("D" & L).Value And .Range("N" & i).Value = .Range("E" & L).Value Then
.Cells(i, j).Value = .Range("A" & L).Value
j = j + 1
End If
Next L
Next i
End With
Sh2.Range("M6:S" & Lr3).Copy Sh1.Range("CY6:DD" & Lr3)

End Sub``````

#### Zacariah171

##### New Member
Thank you for your time you spent working on this. I went a different route and it seems to be working ok. I wound up just making a 5 helper columns, DF7:DJ37. The first column was just a copy of column A. The 2nd helper was just a simple index match formula that looked at column E for "PRI" and returned the value in column D. The 3rd helper column did the same thing with columns J and K, the 4th helper column for columns P and Q, and the 5th helper for columns V and W. So this basically makes the same table as the original but without all the extra columns and it only displays the Units that had PRI next to them in the original table.

Cell Formulas
RangeFormula
DF7:DF38DF7=A7
DG7:DG38DG7=IFNA(INDEX(\$D7, MATCH('Data Validation Table'!\$C\$3, \$E7, 0)), "")
DH7:DH38DH7=IFNA(INDEX(\$J7, MATCH('Data Validation Table'!\$C\$3, \$K7, 0)), "")
DI7:DI38DI7=IFNA(INDEX(\$P7, MATCH('Data Validation Table'!\$C\$3, \$Q7, 0)), "")
DJ7:DJ38DJ7=IFNA(INDEX(\$V7, MATCH('Data Validation Table'!\$C\$3, \$W7, 0)), "")

Next, I recreated the same table that was in my second post (the one that listed the 1st, 2nd, 3, 4, and 5th boxes) and put it in DM7:DR37. The formula I used in DM to search through DF7:DJ37 and list only the values that were listed more than once was
Excel Formula:
``````=IFNA(IFERROR(IFERROR(IFERROR(
LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DG\$7:\$DG\$38)=0)*(COUNTIF(\$DG\$7:\$DG\$38, \$DG\$7:\$DG\$38)>1)), \$DG\$7:\$DG\$38),
LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DH\$7:\$DH\$38)=0)*((COUNTIF(\$DH\$7:\$DH\$38, \$DH\$7:\$DH\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DH\$7:\$DH\$38)>0))),\$DH\$7:\$DH\$38)),
LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DI\$7:\$DI\$38)=0)*((COUNTIF(\$DI\$7:\$DI\$38, \$DI\$7:\$DI\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DI\$7:\$DI\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DI\$7:\$DI\$38)>0))), \$DI\$7:\$DI\$38)),
LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DJ\$7:\$DJ\$38)=0)*((COUNTIF(\$DJ\$7:\$DJ\$38, \$DJ\$7:\$DJ\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DI\$7:\$DI\$38, \$DJ\$7:\$DJ\$38)>0))), \$DJ\$7:\$DJ\$38)), "")``````

Then, the formula I used in columns DN:DR to list the boxes associated was
Excel Formula:
``=IF(\$DM7="", "", IFERROR(INDEX(\$DF\$7:\$DF\$38,SMALL(IF(\$DG\$7:\$DJ\$38=\$DM7,ROW(\$DF\$7:\$DF\$38)-MIN(ROW(\$DF\$7:\$DF\$38))+1),COLUMNS(\$DM7:DM7))),""))``

Cell Formulas
RangeFormula
DM7:DM36DM7=IFNA(IFERROR(IFERROR(IFERROR( LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DG\$7:\$DG\$38)=0)*(COUNTIF(\$DG\$7:\$DG\$38, \$DG\$7:\$DG\$38)>1)), \$DG\$7:\$DG\$38), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DH\$7:\$DH\$38)=0)*((COUNTIF(\$DH\$7:\$DH\$38, \$DH\$7:\$DH\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DH\$7:\$DH\$38)>0))),\$DH\$7:\$DH\$38)), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DI\$7:\$DI\$38)=0)*((COUNTIF(\$DI\$7:\$DI\$38, \$DI\$7:\$DI\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DI\$7:\$DI\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DI\$7:\$DI\$38)>0))), \$DI\$7:\$DI\$38)), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM6, \$DJ\$7:\$DJ\$38)=0)*((COUNTIF(\$DJ\$7:\$DJ\$38, \$DJ\$7:\$DJ\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DI\$7:\$DI\$38, \$DJ\$7:\$DJ\$38)>0))), \$DJ\$7:\$DJ\$38)), "")
DN7:DR38DN7=IF(\$DM7="", "", IFERROR(INDEX(\$DF\$7:\$DF\$38,SMALL(IF(\$DG\$7:\$DJ\$38=\$DM7,ROW(\$DF\$7:\$DF\$38)-MIN(ROW(\$DF\$7:\$DF\$38))+1),COLUMNS(\$DM7:DM7))),""))
DM37:DM38DM37=IFNA(IFERROR(IFERROR(IFERROR( LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM35, \$DG\$7:\$DG\$38)=0)*(COUNTIF(\$DG\$7:\$DG\$38, \$DG\$7:\$DG\$38)>1)), \$DG\$7:\$DG\$38), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM35, \$DH\$7:\$DH\$38)=0)*((COUNTIF(\$DH\$7:\$DH\$38, \$DH\$7:\$DH\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DH\$7:\$DH\$38)>0))),\$DH\$7:\$DH\$38)), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM35, \$DI\$7:\$DI\$38)=0)*((COUNTIF(\$DI\$7:\$DI\$38, \$DI\$7:\$DI\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DI\$7:\$DI\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DI\$7:\$DI\$38)>0))), \$DI\$7:\$DI\$38)), LOOKUP(2, 1/((COUNTIF(\$DM\$6:DM35, \$DJ\$7:\$DJ\$38)=0)*((COUNTIF(\$DJ\$7:\$DJ\$38, \$DJ\$7:\$DJ\$38)>1)+(COUNTIF(\$DG\$7:\$DG\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DH\$7:\$DH\$38, \$DJ\$7:\$DJ\$38)>0)+(COUNTIF(\$DI\$7:\$DI\$38, \$DJ\$7:\$DJ\$38)>0))), \$DJ\$7:\$DJ\$38)), "")

##### Well-known Member
Glad You can Do it. Good Luck.

Replies
2
Views
97
Replies
3
Views
145
Replies
3
Views
142
Replies
1
Views
288
Replies
6
Views
65

1,129,989
Messages
5,639,405
Members
417,086
Latest member
bfruge

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back