Extract unique values from a range based on a matching cell in that row

S

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
42
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good day!

I am looking to pull the unique values from a range (D1:K27) but only if the values in that range match the contents of a cell in that specific row (A1:A27).

I have pasted an example with the results I would like, the formulas would go in column N and carry over to the right.
I was messing around with the TOCOL formula in N9, but it misses the "BB" and gives an #N/A.
I can sort the results later A-Z.
Any help would be much appreciated, thanks in advance!

Book1
ABCDEFGHIJKLMNOPQRSTUV
1PRO 4AAAAAAAAAAAAAAAA
2PRO 1AAAAAAAAAAAAAAAAPersonnelAABBCCDD
3PRO 4BBAABBAADDDDDDDD
4PRO 3BBAABBAAAAAAAAAAPRO 1AA
5PRO 3BBAABBAAAAAAAAAAPRO 2AABBCCDD
6PRO 2BBAABBAADDDDDDDDPRO 3AABB
7PRO 4DDDDDDDDDDDDDDDDPRO 4AABBDD
8PRO 1AAAAAAAAAAAAAAAA
9PRO 2AAAAAAAACCCCCCCCPRO 4AADD#N/A
10PRO 3AAAAAAAAAAAAAAAA
11PRO 2BBBBBBBBBBBBBBBB
12PRO 2BBBBBBBBBBBBBBBB
13PRO 4BBBBBBBBBBBBBBBB
14PRO 2BBBBBBBBBBBBBBBB
15PRO 4BBBBBBBBBBBBBBBB
16PRO 2CCCCCCCCCCCCCCCC
17PRO 1AAAAAAAAAAAAAAAA
18PRO 2DDDDDDDDDDDDDDDD
19PRO 2AAAAAAAAAAAAAAAA
20PRO 3AAAAAAAAAAAAAAAA
21PRO 3BBBBBBBBAAAAAAAA
22PRO 3BBBBBBBBAAAAAAAA
23PRO 4BBBBBBBBAAAAAAAA
24PRO 4BBBBBBBBAAAAAAAA
25PRO 2BBBBBBBBAAAAAAAA
26PRO 1AAAAAAAAAAAAAAAA
27PRO 3BBBBBBBBAAAAAAAA
Sheet1
Cell Formulas
RangeFormula
N2:Q2N2=TRANSPOSE(SORT(UNIQUE(TOCOL($D$1:$K$27,3)),1,1))
N9:Q9N9=TRANSPOSE(UNIQUE(IF($A$1:$A$27=$M9,TOCOL($D$1:$K$27,3),"")))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet1!$A$1:$K$27N9
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
it can be done via VBA if you are interested

VBA Code: 
Sub RangeMatch()
    For c = 4 To 11
        For r = 1 To 27
            Post Cells(r, 1).Value, Cells(r, c).Value
        Next r
    Next c
End Sub

Private Sub Post(ByVal personel As String, ByVal code As String)
    Select Case code
    Case "AA"
        cl = "N"
    Case "BB"
        cl = "O"
    Case "CC"
        cl = "P"
    Case "DD"
        cl = "Q"
    End Select
    
    Select Case personel
    Case "PRO 1"
        rw = 4
    Case "PRO 2"
        rw = 5
    Case "PRO 3"
        rw = 6
    Case "PRO 4"
        rw = 7
    End Select
    
    Range(cl & rw).Value = code
End Sub
 
Upvote 0
Hi Iggy,

The problem with that is the values are hard coded, my actual log has sometimes 30 different "PRO's" and the AA, BB, CC, DD are symbols for people doing the work and change from project to project, sometimes there are 50 or more symbols to deal with.
Thanks for the reply!
 
Upvote 0
How about this then: (Not knowing how many columns the data might occupy, I'm putting the result below the data. Also I'm assuming the data starts on A1)

VBA Code: 
Sub RangeMatch()
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    For c = 2 To lastCol
        For r = 1 To lastRow
            Post Cells(r, 1).Value, Cells(r, c).Value, lastRow
        Next r
    Next c
    MsgBox "Done!"
End Sub

Private Sub Post(ByVal personel As String, ByVal job As String, ByVal lr As Long)
    startRow = lr + 2
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    foundRow = 0
    foundJob = False
   
    If lastRow >= startRow Then 'Search for personnel
        For r = startRow To lastRow
            If Range("A" & r).Value = personel Then
                foundRow = r
                Exit For
            End If
        Next r
       
        If foundRow > 0 Then 'Search for job
            c = 2
            Do While Trim(Cells(foundRow, c).Value) <> ""
                If Cells(foundRow, c).Value = job Then
                    foundJob = True
                End If
                If foundJob Then Exit Do
                c = c + 1
            Loop
            If Not foundJob Then 'If job not found then add
                Cells(foundRow, Cells(foundRow, Columns.Count).End(xlToLeft).Column + 1).Value = job
            End If
        Else 'If personnel not found then add
            Range("A" & lastRow + 1).Value = personel
            Range("B" & lastRow + 1).Value = job
        End If
       
    Else 'Initial entry
        Range("A" & startRow).Value = personel
        Range("B" & startRow).Value = job
    End If
End Sub
 
Upvote 0
Hi Iggy,

That does the job, my problem is that I have very basic knowledge of VBA and have a hard time deconstructing your code to make it work in my log. The actual log I am using usually has about 2000 rows of data and is cross referenced from the sheet with this code.
I need to have the PRO numbers in descending order and the AA, BB's in alphabetical order starting in a specific cell. I do understand the lr +2 to start where the data is placed but that's about it.
I don't want to sound ungrateful for your efforts but I'm kind of lost with the rest of it; I'm more looking for a formula that will work in this situation.
 
Upvote 0
Could you use something like this?

23 08 08.xlsm
ABCDEFGHIJKLMNOPQR
1PRO 4AAAAAAAAAAAAAAAA
2PRO 1AAAAAAAAAAAAAAAAPRO 4AABBDD
3PRO 4BBAABBAADDDDDDDDPRO 1AA
4PRO 3BBAABBAAAAAAAAAAPRO 3BBAA
5PRO 3BBAABBAAAAAAAAAAPRO 2BBAADDCC
6PRO 2BBAABBAADDDDDDDD 
7PRO 4DDDDDDDDDDDDDDDD 
8PRO 1AAAAAAAAAAAAAAAA
9PRO 2AAAAAAAACCCCCCCC
10PRO 3AAAAAAAAAAAAAAAA
11PRO 2BBBBBBBBBBBBBBBB
12PRO 2BBBBBBBBBBBBBBBB
13PRO 4BBBBBBBBBBBBBBBB
14PRO 2BBBBBBBBBBBBBBBB
15PRO 4BBBBBBBBBBBBBBBB
16PRO 2CCCCCCCCCCCCCCCC
17PRO 1AAAAAAAAAAAAAAAA
18PRO 2DDDDDDDDDDDDDDDD
19PRO 2AAAAAAAAAAAAAAAA
20PRO 3AAAAAAAAAAAAAAAA
21PRO 3BBBBBBBBAAAAAAAA
22PRO 3BBBBBBBBAAAAAAAA
23PRO 4BBBBBBBBAAAAAAAA
24PRO 4BBBBBBBBAAAAAAAA
25PRO 2BBBBBBBBAAAAAAAA
26PRO 1AAAAAAAAAAAAAAAA
27PRO 3BBBBBBBBAAAAAAAA
Sheet2 (2)
Cell Formulas
RangeFormula
M2:M5M2=UNIQUE(A1:A27)
N2:P2,N5:Q5,N4:O4,N3,N6:N7N2=IF(M2="","",UNIQUE(TOROW(IF(A$1:A$27=M2,D$1:K$27,x),2),1))
Dynamic array formulas.
 
Upvote 1
Hi Peter, thanks for your response.
Your formula works however when I tried to add the sort function I couldn't get it to sort properly. I tried adding it in different locations within the formula and the best I could get was in the one marked #4 in the example below; it still would not properly sort for PRO 2.
I found that if I used the TOCOL and transpose instead of TOROW that it sorted properly, I am not sure why this is...
Anyways, thanks for the help, I got the results I needed in cells N27 to N30.

Mr. Excel Extract from range.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1PRO 4AAAAAAAAAAAAAAAA
2PRO 1AAAAAAAAAAAAAAAAOriginalPRO 1AA
3PRO 4BBAABBAADDDDDDDDPRO 2BBAADDCC
4PRO 3BBAABBAAAAAAAAAAPRO 3BBAA
5PRO 3BBAABBAAAAAAAAAAPRO 4AABBDD
6PRO 2BBAABBAADDDDDDDD
7PRO 4DDDDDDDDDDDDDDDD1PRO 1AA
8PRO 1AAAAAAAAAAAAAAAAPRO 2BBAADDCC
9PRO 2AAAAAAAACCCCCCCCPRO 3BBAA
10PRO 3AAAAAAAAAAAAAAAAPRO 4AABBDD
11PRO 2BBBBBBBBBBBBBBBB
12PRO 2BBBBBBBBBBBBBBBB2PRO 1AA
13PRO 4BBBBBBBBBBBBBBBBPRO 2BBAADDCC
14PRO 2BBBBBBBBBBBBBBBBPRO 3BBAA
15PRO 4BBBBBBBBBBBBBBBBPRO 4AABBDD
16PRO 2CCCCCCCCCCCCCCCC
17PRO 1AAAAAAAAAAAAAAAA3PRO 1AA
18PRO 2DDDDDDDDDDDDDDDDPRO 2BBAADDCC
19PRO 2AAAAAAAAAAAAAAAAPRO 3BBAA
20PRO 3AAAAAAAAAAAAAAAAPRO 4AABBDD
21PRO 3BBBBBBBBAAAAAAAA
22PRO 3BBBBBBBBAAAAAAAA4PRO 1AA
23PRO 4BBBBBBBBAAAAAAAAPRO 2AACCBBDD
24PRO 4BBBBBBBBAAAAAAAAPRO 3AABB
25PRO 2BBBBBBBBAAAAAAAAPRO 4AABBDD
26PRO 1AAAAAAAAAAAAAAAA
27PRO 3BBBBBBBBAAAAAAAA5PRO 1AA
28PRO 2AABBCCDD
29PRO 3AABB
30PRO 4AABBDD
31
Sheet1
Cell Formulas
RangeFormula
M2:M5,M27:M30,M22:M25,M17:M20,M12:M15,M7:M10M2=SORT(UNIQUE(A$1:A$27),,1)
N2,N5:P5,N4:O4,N3:Q3N2=IF(M2="","",UNIQUE(TOROW(IF(A$1:A$27=M2,D$1:K$27,x),2),1))
N7,N10:P10,N9:O9,N8:Q8N7=SORT(IF(M7="","",UNIQUE(TOROW(IF(A$1:A$27=M7,D$1:K$27,x),2),1)),1,1)
N12,N15:P15,N14:O14,N13:Q13N12=IF(M12="","",SORT(UNIQUE(TOROW(IF(A$1:A$27=M12,D$1:K$27,x),2),1),1,1))
N17,N20:P20,N19:O19,N18:Q18N17=IF(M17="","",UNIQUE(SORT(TOROW(IF(A$1:A$27=M17,D$1:K$27,x),2),1,1),1))
N22,N25:P25,N24:O24,N23:Q23N22=IF(M22="","",UNIQUE(TOROW(SORT(IF(A$1:A$27=M22,D$1:K$27,x),,1),2),1))
N27,N30:P30,N29:O29,N28:Q28N27=TRANSPOSE(SORT(UNIQUE(TOCOL(IF(A$1:A$27=M27,D$1:K$27,x),2)),,1))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet1!$A$1:$K$27M27, M22, M17, M12, M7, M2, N2:N5, N7:N10, N12:N15, N17:N20, N22:N25, N27:N30
 
Upvote 0
Silverhorne said:
Your formula works however when I tried to add the sort function I couldn't get it to sort properly.
Click to expand...
Try
23 08 08.xlsm
ABCDEFGHIJKLMNOPQR
1PRO 4AAAAAAAAAAAAAAAA
2PRO 1AAAAAAAAAAAAAAAAPRO 4AABBDD
3PRO 4BBAABBAADDDDDDDDPRO 1AA
4PRO 3BBAABBAAAAAAAAAAPRO 3AABB
5PRO 3BBAABBAAAAAAAAAAPRO 2AABBCCDD
6PRO 2BBAABBAADDDDDDDD 
7PRO 4DDDDDDDDDDDDDDDD 
8PRO 1AAAAAAAAAAAAAAAA
9PRO 2AAAAAAAACCCCCCCC
10PRO 3AAAAAAAAAAAAAAAA
11PRO 2BBBBBBBBBBBBBBBB
12PRO 2BBBBBBBBBBBBBBBB
13PRO 4BBBBBBBBBBBBBBBB
14PRO 2BBBBBBBBBBBBBBBB
15PRO 4BBBBBBBBBBBBBBBB
16PRO 2CCCCCCCCCCCCCCCC
17PRO 1AAAAAAAAAAAAAAAA
18PRO 2DDDDDDDDDDDDDDDD
19PRO 2AAAAAAAAAAAAAAAA
20PRO 3AAAAAAAAAAAAAAAA
21PRO 3BBBBBBBBAAAAAAAA
22PRO 3BBBBBBBBAAAAAAAA
23PRO 4BBBBBBBBAAAAAAAA
24PRO 4BBBBBBBBAAAAAAAA
25PRO 2BBBBBBBBAAAAAAAA
26PRO 1AAAAAAAAAAAAAAAA
27PRO 3BBBBBBBBAAAAAAAA
With Sort
Cell Formulas
RangeFormula
M2:M5M2=UNIQUE(A1:A27)
N2:P2,N5:Q5,N4:O4,N3,N6:N7N2=IF(M2="","",SORT(UNIQUE(TOROW(IF(A$1:A$27=M2,D$1:K$27,x),2),1),,,1))
Dynamic array formulas.
 
Upvote 1
Solution
Cheers. You just didn't quite have the right arguments in the right places in the SORT function. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
356
MARZIOTULLIO
M
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
156
Domenic
D
Cubist
  • Solved
Taking the averaging of the last 3 values.
Replies
3
Views
361
RoryA
RoryA
F
  • Solved
Formula to show in 4 columns the letters from one column with condition
Replies
6
Views
300
Fractalis
F
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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