For every similar column C add the first value from column B in column G

[NessPJ]

Hi guys,

I'm trying to figure out if i can have a formula that will put the first value from column B in column G for every similar value in column C.

I have a file with testdata uploaded here: https://www.dropbox.com/s/cehp2rmi5ao5tta/Testfile01.xlsx?dl=0

So basically the column Route (C) has several values "101". For every one of those values i would like Column G to contain the value "23" (which is the first value to come across in the table for Route 101).

Is this possible?

 

[NessPJ]

Yup, just record a macro whilst you do it manually

That is actually pretty efficient.

ActiveSheet.Range("$A$1:$L$3086").RemoveDuplicates Columns:=Array(4, 5), _
Header:=xlYes

Can you confirm that with "Columns:=Array(4, 5)," Only duplicates from Column 4 of every duplicate value within Column 5 are removed?

 

[Fluff]

That will delete any row where the values in both cols D & E are duplicated.
So in this example the green row will be deleted, but the blue row wont.

Excel 2013/2016
	A	B	C	D	E
1	Key	Sequence	Day	Block	Route
2	32441323	1	1	1000	105
3	3243214	1	1	1645	105
4	12342314	1	1	2200	105
5	21343214	1	1	1002	105
6	23141234	1	1	1100	102
7	21342341	1	1	1101	102
8	1234321	1	1	1000	102
9	12343214	1	1	1001	112
10	23141234	2	1	1007	112
11	12342134	1	1	3107	112
12	23141234	2	1	3107	112
13	32141234	1	1	3107	105
14	12341234	2	1	1245	108
15	23142134	1	1	2201	108
Sheet1

 

[NessPJ]

I can not seem to catch a break today.

If i try to incorporate my duplicate check, i keep getting Type Mismatch errors.
I am probably doing something while trying to check if my value is "J" or "j" but i can't figure out what it is.
What am i doing wrong?

    Dim SKIP2deLEV As Boolean
    Dim CHK2deLEV As Variant
    
    CHK2deLEV = Sheets("Par").Range("B38").Value
    
    If CHK2deLEV Is Empty Then
        SKIP2deLEV = True
    End If
    
    If Not CHK2deLEV Is "J" Or "j" Then
        SKIP2deLEV = True
    End If
    
    If Not SKIP2deLEV = True Then
        ActiveSheet.Range("$A$3:$M$" & LastRowATImpTable).RemoveDuplicates Columns:=Array(4, 5), _
        Header:=xlYes
    End If

 

[Fluff]

Try

   Dim SKIP2deLEV As Boolean
   Dim CHK2deLEV As Variant
   
   CHK2deLEV = Sheets("Par").Range("B38").Value
   
   If CHK2deLEV = "" Or LCase(CHK2deLEV) = "j" Then
      SKIP2deLEV = True
   End If
   If Not SKIP2deLEV = True Then
      ActiveSheet.Range("$A$3:$M$" & LastRowATImpTable).RemoveDuplicates Columns:=Array(4, 5), _
      Header:=xlYes
   End If