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

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
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
    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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,828
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
 

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
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
Joined
Apr 2, 2019
Messages
25
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))),""))
1611759086676.png
 

Attachments

  • 1611759012338.png
    1611759012338.png
    11.9 KB · Views: 0

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,828
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
1IDReceivedReturnedUnitUnit TypeStatusStartEndDaysLocationCountifsUnitStatus1234
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
Joined
Apr 2, 2019
Messages
25
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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,828
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,828
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Apr 2, 2019
Messages
25
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)), "")
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,828
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Glad You can Do it. Good Luck.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top