Help formula: Return value from left for duplicate values

D

DarkSmile

Board Regular
Joined
Feb 22, 2021
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to accomplish the following, I have some formulas to detect if there are duplicate values:
202110 - Approve Quotation check v2 -.xlsm
AGAHAI
30Duplicate audittimes?
31Is there a number higher then 0 in 2021?OK
32Is there a duplicate value in 2021?TRUE
Calculatie
Cell Formulas
RangeFormula
AH31AH31=IF(AND($AG$5="0",$AG$6="0",$AG$7="0",$AG$8="0",$AG$9="0",$AG$10="0",$AG$11="0",$AG$12="0",$AG$13="0",$AG$14="0"),"N/A","OK")
AH32AH32=IF($AH$31="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0)


I would like to say now: if there is a duplicate value get name from the left of it, in this example it should return "ACS-002 Ind Lait & ACS-005 glaces-ijs". There may be more than 2 duplicate values, so it needs to sum them all. Is this possible? And if so can anyone help me with a formula?

Also looking for a formula that tells me if the duplicate value is the same as the highest number (green cell)


202110 - Approve Quotation check v3 -.xlsm
AFAGAH
42021
5ACS-002 Ind Lait0
6ACS-005 glaces-ijs1
7GRMS1
800
900
1000
1100
1200
1300
1400
151
16
17Duplicate audittimes?
18Is there a number higher then 0 in 2021?OK
19Is there a duplicate value in 2021?TRUE
20What's duplicate (name)?
21Is duplicate number same as highest number?
Calculatie
Cell Formulas
RangeFormula
AF5AF5=$B$4
AF6AF6=$B$10
AF7AF7=$B$16
AF8AF8=$B$22
AF9AF9=$B$28
AF10AF10=$B$34
AF11AF11=$B$40
AF12AF12=$B$46
AF13AF13=$B$52
AF14AF14=$B$58
AG5AG5=$G$6
AG6AG6=$G$12
AG7AG7=$G$18
AG8AG8=$G$24
AG9AG9=$G$30
AG10AG10=$G$36
AG11AG11=$G$42
AG12AG12=$G$48
AG13AG13=$G$54
AG14AG14=$G$60
AG15AG15=MAX($AG$5,$AG$6,$AG$7,$AG$8,$AG$9,$AG$10,$AG$11,$AG$12,$AG$13,$AG$14)
AH18AH18=IF(AND($AG$5="0",$AG$6="0",$AG$7="0",$AG$8="0",$AG$9="0",$AG$10="0",$AG$11="0",$AG$12="0",$AG$13="0",$AG$14="0"),"N/A","OK")
AH19AH19=IF($AH$18="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0)
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Why "ACS-002 Ind Lait & ACS-005 glaces-ijs"? it is 0 & 1 (not duplicate, its unique)

Why not "ACS-002 Ind Lait & GRMS"? it is 1 & 1 (duplicate 1)

VBA is a good choice. Does it accepted?
 
Upvote 0
bebo021999 said:
Why "ACS-002 Ind Lait & ACS-005 glaces-ijs"? it is 0 & 1 (not duplicate, its unique)

Why not "ACS-002 Ind Lait & GRMS"? it is 1 & 1 (duplicate 1)

VBA is a good choice. Does it accepted?
Click to expand...
My bad, it's indeed "ACS-002 Ind Lait & GRMS". VBA is ok yes
 
Upvote 0
Alt-F11 to open VBA window. Insert "module" with below code:
VBA Code: 
Option Explicit
Sub test()
Dim i&, j&
Dim st As String
If Not [AH19].Value Then Exit Sub
    For i = 5 To 13
        For j = i + 1 To 14
            If Cells(i, "AG") > 0 And Cells(i, "AG") = Cells(j, "AG") Then
                st = Replace(st & "&" & IIf(InStr(st, Cells(i, "AF")) > 0, "", Cells(i, "AF")) & "&" & _
                IIf(InStr(st, Cells(j, "AF")) > 0, "", Cells(j, "AF")), "&&", "&")
            End If
        Next
    Next
[AH20].Value = Replace(IIf(Right(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255), 1) = "&", Left(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255), _
Len(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255)) - 1), Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255)), "&", " & ")
End Sub

Book1
AFAGAH
42021
5ACS-002 Ind Lait0
6ACS-005 glaces-ijs1
7GRMS1
8aaa0
900
1000
1100
1200
1300
1400
151
16
17Duplicate audittimes?
18Is there a number higher then 0 in 2021?OK
19Is there a duplicate value in 2021?TRUE
20What's duplicate (name)? ACS-005 glaces-ijs & GRMS
21Is duplicate number same as highest number?Yes
Sheet1
Cell Formulas
RangeFormula
AF5AF5=$B$4
AF6AF6=$B$10
AF7AF7=$B$16
AF8AF8=$B$22
AF9AF9=$B$28
AF10AF10=$B$34
AF11AF11=$B$40
AF12AF12=$B$46
AF13AF13=$B$52
AF14AF14=$B$58
AG5AG5=$G$6
AG6AG6=$G$12
AG7AG7=$G$18
AG8AG8=$G$24
AG9AG9=$G$30
AG10AG10=$G$36
AG11AG11=$G$42
AG12AG12=$G$48
AG13AG13=$G$54
AG14AG14=$G$60
AG15AG15=MAX(AG5:AG14)
AH18AH18=IF(COUNTIF(AG5:AG14,">0"),"OK","")
AH19AH19=IF($AH$18="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0)
AH21AH21=IF(COUNTIF($AG$5:$AG$14,AG15)>1,"Yes","")
 
Last edited:
Upvote 0
Solution
bebo021999 said:
Alt-F11 to open VBA window. Insert "module" with below code:
VBA Code: 
Option Explicit
Sub test()
Dim i&, j&
Dim st As String
If Not [AH19].Value Then Exit Sub
    For i = 5 To 13
        For j = i + 1 To 14
            If Cells(i, "AG") > 0 And Cells(i, "AG") = Cells(j, "AG") Then
                st = Replace(st & "&" & IIf(InStr(st, Cells(i, "AF")) > 0, "", Cells(i, "AF")) & "&" & _
                IIf(InStr(st, Cells(j, "AF")) > 0, "", Cells(j, "AF")), "&&", "&")
            End If
        Next
    Next
[AH20].Value = Replace(IIf(Right(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255), 1) = "&", Left(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255), _
Len(Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255)) - 1), Mid(st, IIf(Left(st, 1) = "&", 2, 1), 255)), "&", " & ")
End Sub

Book1
AFAGAH
42021
5ACS-002 Ind Lait0
6ACS-005 glaces-ijs1
7GRMS1
8aaa0
900
1000
1100
1200
1300
1400
151
16
17Duplicate audittimes?
18Is there a number higher then 0 in 2021?OK
19Is there a duplicate value in 2021?TRUE
20What's duplicate (name)? ACS-005 glaces-ijs & GRMS
21Is duplicate number same as highest number?Yes
Sheet1
Cell Formulas
RangeFormula
AF5AF5=$B$4
AF6AF6=$B$10
AF7AF7=$B$16
AF8AF8=$B$22
AF9AF9=$B$28
AF10AF10=$B$34
AF11AF11=$B$40
AF12AF12=$B$46
AF13AF13=$B$52
AF14AF14=$B$58
AG5AG5=$G$6
AG6AG6=$G$12
AG7AG7=$G$18
AG8AG8=$G$24
AG9AG9=$G$30
AG10AG10=$G$36
AG11AG11=$G$42
AG12AG12=$G$48
AG13AG13=$G$54
AG14AG14=$G$60
AG15AG15=MAX(AG5:AG14)
AH18AH18=IF(COUNTIF(AG5:AG14,">0"),"OK","")
AH19AH19=IF($AH$18="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0)
AH21AH21=IF(COUNTIF($AG$5:$AG$14,AG15)>1,"Yes","")
Click to expand...
Thank you for the VBA code & formula, don't I need to set a formula or something in cell "AH20" (What's duplicate (name)?) ? Just VBA code doesn't do anything.

202110 - Approve Quotation check v3 -.xlsm
AGAH
17Duplicate audittimes?
18Is there a number higher then 0 in 2021?OK
19Is there a duplicate value in 2021?TRUE
20What's duplicate (name)?
21Is duplicate number same as highest number?No
Calculatie
Cell Formulas
RangeFormula
AH18AH18=IF(AND($AG$5="0",$AG$6="0",$AG$7="0",$AG$8="0",$AG$9="0",$AG$10="0",$AG$11="0",$AG$12="0",$AG$13="0",$AG$14="0"),"N/A","OK")
AH19AH19=IF($AH$18="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0)
AH21AH21=IF(COUNTIF($AG$5:$AG$14,AG15)>1,"Yes","No")
 
Upvote 0
You must log in or register to reply here.

Similar threads

datastudent
  • Solved
Consolidate duplicate Names
Replies
6
Views
103
Fluff
Fluff
J
  • Solved
help finding my error
Replies
1
Views
145
Proulxs
P
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
122
KRice
K
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
167
Tetra201
Tetra201
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
126
gtd526
gtd526

Forum statistics

Threads
1,214,827
Messages
6,121,821
Members
449,049
Latest member
cybersurfer5000

Share this page

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
Back
Top