VBA for Adding Unique Values in Row to Column D

BraytonM · Jul 26, 2021

Hello all,

I am looking for a VBA to do the following: add the unique values in rows that aren't blank to column D. Column C adds up the unique values per row (range R:AQ) and I have a macro that adds X values above based on that value.

I have attached a before and after of what I would like the macro to do. Hopefully, y'all can help me!

DanteAmor · Jul 26, 2021

Try this:

VBA Code:

Sub AddUniqueValues()
  Dim a As Range, dic As Object, j As Long
  For Each a In Range("A2", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants)
    Set dic = CreateObject("Scripting.Dictionary")
    For j = 4 To Cells(a.Row, Columns.Count).End(1).Column
      If Cells(a.Row, j) <> "" Then dic(Cells(a.Row, j).Value) = Empty
    Next
    a.Offset(dic.Count * -1, 2).Resize(dic.Count).Value = Application.Transpose(dic.keys)
  Next
End Sub

BraytonM · Jul 26, 2021

DanteAmor said:

Try this:

VBA Code:

Sub AddUniqueValues()
  Dim a As Range, dic As Object, j As Long
  For Each a In Range("A2", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants)
    Set dic = CreateObject("Scripting.Dictionary")
    For j = 4 To Cells(a.Row, Columns.Count).End(1).Column
      If Cells(a.Row, j) <> "" Then dic(Cells(a.Row, j).Value) = Empty
    Next
    a.Offset(dic.Count * -1, 2).Resize(dic.Count).Value = Application.Transpose(dic.keys)
  Next
End Sub

Hello @DanteAmor, I tried this and got a Run-time error 1004 error: no cells were found.

DanteAmor · Jul 26, 2021

The data structure of your test is not similar to the data structure of your example.
You can put a sample of your test data here. It would be ideal if you put them using the XL2BB tool minisheets.

BraytonM · Jul 26, 2021

DanteAmor said:
The data structure of your test is not similar to the data structure of your example.
You can put a sample of your test data here. It would be ideal if you put them using the XL2BB tool minisheets.

Perhaps so, let me download XL2BB tool minisheet and I will get back to this! Thank you so much for you help!

BraytonM · Jul 27, 2021

I have added a mini-spreadsheet (hopefully this works haha). Please note the correct range is V2:AU and that all unique values in non-blank rows should be added to Column D. Thank you. @DanteAmor

PR TEMPLATE AUTOMATION EXAMPLE.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

1

Employee No

DELETE ME

DELETE COUNT

Job No

Phase No

Cost Code

Union No

Earn Code

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

Sheet1

Cell Formulas
Range	Formula
I1:N1	I1	=J1-1
O1	O1	=Timesheet!C7
V1:X1	V1	=$I$1
Y1:AB1	Y1	=$J$1
AC1:AF1	AC1	=$K$1
AG1:AJ1	AG1	=$L$1
AK1:AN1	AK1	=$M$1
AO1:AR1	AO1	=$N$1
AS1:AU1	AS1	=$O$1
A5	A5	=IF(Timesheet!AI15=0,"",Timesheet!$C$15)
B5,B15	B5	=IF(A5<>"","ST","")
C5,C21,C19,C17,C15,C11,C8	C5	=SUMPRODUCT((V5:AU5<>"")/COUNTIF(V5:AU5,V5:AU5&""))
V5	V5	=IF(Timesheet!H15>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W5	W5	=IF(Timesheet!I15>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X5	X5	=IF(Timesheet!J15>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y5	Y5	=IF(Timesheet!K15>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z5	Z5	=IF(Timesheet!L15>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA5	AA5	=IF(Timesheet!M15>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB5	AB5	=IF(Timesheet!N15>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC5	AC5	=IF(Timesheet!O15>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD5	AD5	=IF(Timesheet!P15>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE5	AE5	=IF(Timesheet!Q15>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF5	AF5	=IF(Timesheet!R15>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG5	AG5	=IF(Timesheet!S15>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH5	AH5	=IF(Timesheet!T15>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI5	AI5	=IF(Timesheet!U15>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ5	AJ5	=IF(Timesheet!V15>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK5	AK5	=IF(Timesheet!W15>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL5	AL5	=IF(Timesheet!X15>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM5	AM5	=IF(Timesheet!Y15>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN5	AN5	=IF(Timesheet!Z15>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO5	AO5	=IF(Timesheet!AA15>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP5	AP5	=IF(Timesheet!AB15>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ5	AQ5	=IF(Timesheet!AC15>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR5	AR5	=IF(Timesheet!AD15>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS5	AS5	=IF(Timesheet!AE15>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT5	AT5	=IF(Timesheet!AF15>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU5	AU5	=IF(Timesheet!AG15>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A8	A8	=IF(Timesheet!AI16=0,"",Timesheet!$C$15)
B8	B8	=IF($A$8<>"","RH","")
V8	V8	=IF(Timesheet!H16>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W8	W8	=IF(Timesheet!I16>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X8	X8	=IF(Timesheet!J16>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y8	Y8	=IF(Timesheet!K16>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z8	Z8	=IF(Timesheet!L16>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA8	AA8	=IF(Timesheet!M16>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB8	AB8	=IF(Timesheet!N16>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC8	AC8	=IF(Timesheet!O16>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD8	AD8	=IF(Timesheet!P16>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE8	AE8	=IF(Timesheet!Q16>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF8	AF8	=IF(Timesheet!R16>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG8	AG8	=IF(Timesheet!S16>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH8	AH8	=IF(Timesheet!T16>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI8	AI8	=IF(Timesheet!U16>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ8	AJ8	=IF(Timesheet!V16>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK8	AK8	=IF(Timesheet!W16>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL8	AL8	=IF(Timesheet!X16>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM8	AM8	=IF(Timesheet!Y16>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN8	AN8	=IF(Timesheet!Z16>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO8	AO8	=IF(Timesheet!AA16>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP8	AP8	=IF(Timesheet!AB16>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ8	AQ8	=IF(Timesheet!AC16>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR8	AR8	=IF(Timesheet!AD16>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS8	AS8	=IF(Timesheet!AE16>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT8	AT8	=IF(Timesheet!AF16>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU8	AU8	=IF(Timesheet!AG16>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A11	A11	=IF(Timesheet!AI17=0,"",Timesheet!$C$15)
B11	B11	=IF($A$11<>"","DT","")
V11	V11	=IF(Timesheet!H17>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W11	W11	=IF(Timesheet!I17>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X11	X11	=IF(Timesheet!J17>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y11	Y11	=IF(Timesheet!K17>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z11	Z11	=IF(Timesheet!L17>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA11	AA11	=IF(Timesheet!M17>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB11	AB11	=IF(Timesheet!N17>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC11	AC11	=IF(Timesheet!O17>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD11	AD11	=IF(Timesheet!P17>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE11	AE11	=IF(Timesheet!Q17>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF11	AF11	=IF(Timesheet!R17>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG11	AG11	=IF(Timesheet!S17>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH11	AH11	=IF(Timesheet!T17>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI11	AI11	=IF(Timesheet!U17>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ11	AJ11	=IF(Timesheet!V17>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK11	AK11	=IF(Timesheet!W17>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL11	AL11	=IF(Timesheet!X17>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM11	AM11	=IF(Timesheet!Y17>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN11	AN11	=IF(Timesheet!Z17>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO11	AO11	=IF(Timesheet!AA17>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP11	AP11	=IF(Timesheet!AB17>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ11	AQ11	=IF(Timesheet!AC17>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR11	AR11	=IF(Timesheet!AD17>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS11	AS11	=IF(Timesheet!AE17>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT11	AT11	=IF(Timesheet!AF17>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU11	AU11	=IF(Timesheet!AG17>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A15	A15	=IF(Timesheet!AI19=0,"",Timesheet!F19)
V15	V15	=IF(Timesheet!H19>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W15	W15	=IF(Timesheet!I19>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X15	X15	=IF(Timesheet!J19>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y15	Y15	=IF(Timesheet!K19>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z15	Z15	=IF(Timesheet!L19>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA15	AA15	=IF(Timesheet!M19>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB15	AB15	=IF(Timesheet!N19>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC15	AC15	=IF(Timesheet!O19>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD15	AD15	=IF(Timesheet!P19>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE15	AE15	=IF(Timesheet!Q19>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF15	AF15	=IF(Timesheet!R19>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG15	AG15	=IF(Timesheet!S19>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH15	AH15	=IF(Timesheet!T19>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI15	AI15	=IF(Timesheet!U19>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ15	AJ15	=IF(Timesheet!V19>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK15	AK15	=IF(Timesheet!W19>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL15	AL15	=IF(Timesheet!X19>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM15	AM15	=IF(Timesheet!Y19>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN15	AN15	=IF(Timesheet!Z19>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO15	AO15	=IF(Timesheet!AA19>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP15	AP15	=IF(Timesheet!AB19>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ15	AQ15	=IF(Timesheet!AC19>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR15	AR15	=IF(Timesheet!AD19>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS15	AS15	=IF(Timesheet!AE19>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT15	AT15	=IF(Timesheet!AF19>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU15	AU15	=IF(Timesheet!AG19>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A17	A17	=IF(Timesheet!AI20=0,"",Timesheet!F20)
B17	B17	=IF($A$17<>"","RH","")
V17	V17	=IF(Timesheet!H20>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W17	W17	=IF(Timesheet!I20>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X17	X17	=IF(Timesheet!J20>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y17	Y17	=IF(Timesheet!K20>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z17	Z17	=IF(Timesheet!L20>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA17	AA17	=IF(Timesheet!M20>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB17	AB17	=IF(Timesheet!N20>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC17	AC17	=IF(Timesheet!O20>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD17	AD17	=IF(Timesheet!P20>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE17	AE17	=IF(Timesheet!Q20>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF17	AF17	=IF(Timesheet!R20>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG17	AG17	=IF(Timesheet!S20>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH17	AH17	=IF(Timesheet!T20>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI17	AI17	=IF(Timesheet!U20>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ17	AJ17	=IF(Timesheet!V20>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK17	AK17	=IF(Timesheet!W20>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL17	AL17	=IF(Timesheet!X20>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM17	AM17	=IF(Timesheet!Y20>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN17	AN17	=IF(Timesheet!Z20>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO17	AO17	=IF(Timesheet!AA20>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP17	AP17	=IF(Timesheet!AB20>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ17	AQ17	=IF(Timesheet!AC20>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR17	AR17	=IF(Timesheet!AD20>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS17	AS17	=IF(Timesheet!AE20>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT17	AT17	=IF(Timesheet!AF20>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU17	AU17	=IF(Timesheet!AG20>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A19	A19	=IF(Timesheet!AI21=0,"",Timesheet!F21)
B19	B19	=IF(A19<>"","DT","")
V19	V19	=IF(Timesheet!H21>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W19	W19	=IF(Timesheet!I21>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X19	X19	=IF(Timesheet!J21>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y19	Y19	=IF(Timesheet!K21>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z19	Z19	=IF(Timesheet!L21>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA19	AA19	=IF(Timesheet!M21>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB19	AB19	=IF(Timesheet!N21>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC19	AC19	=IF(Timesheet!O21>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD19	AD19	=IF(Timesheet!P21>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE19	AE19	=IF(Timesheet!Q21>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF19	AF19	=IF(Timesheet!R21>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG19	AG19	=IF(Timesheet!S21>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH19	AH19	=IF(Timesheet!T21>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI19	AI19	=IF(Timesheet!U21>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ19	AJ19	=IF(Timesheet!V21>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK19	AK19	=IF(Timesheet!W21>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL19	AL19	=IF(Timesheet!X21>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM19	AM19	=IF(Timesheet!Y21>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN19	AN19	=IF(Timesheet!Z21>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO19	AO19	=IF(Timesheet!AA21>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP19	AP19	=IF(Timesheet!AB21>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ19	AQ19	=IF(Timesheet!AC21>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR19	AR19	=IF(Timesheet!AD21>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS19	AS19	=IF(Timesheet!AE21>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT19	AT19	=IF(Timesheet!AF21>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU19	AU19	=IF(Timesheet!AG21>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")
A21	A21	=IF(Timesheet!AI22=0,"",Timesheet!F22)
B21	B21	=IF($A$21<>"","IW","")
V21	V21	=IF(Timesheet!H22>0,CONCAT(Timesheet!$H$5," ",Timesheet!$H$4,""),"")
W21	W21	=IF(Timesheet!I22>0,CONCAT(Timesheet!$I$5," ",Timesheet!$I$4,""),"")
X21	X21	=IF(Timesheet!J22>0,CONCAT(Timesheet!$J$5," ",Timesheet!$J$4,""),"")
Y21	Y21	=IF(Timesheet!K22>0,CONCAT(Timesheet!$K$5," ",Timesheet!$K$4,""),"")
Z21	Z21	=IF(Timesheet!L22>0,CONCAT(Timesheet!$L$5," ",Timesheet!$L$4,""),"")
AA21	AA21	=IF(Timesheet!M22>0,CONCAT(Timesheet!$M$5," ",Timesheet!$M$4,""),"")
AB21	AB21	=IF(Timesheet!N22>0,CONCAT(Timesheet!$N$5," ",Timesheet!$N$4,""),"")
AC21	AC21	=IF(Timesheet!O22>0,CONCAT(Timesheet!$O$5," ",Timesheet!$O$4,""),"")
AD21	AD21	=IF(Timesheet!P22>0,CONCAT(Timesheet!$P$5," ",Timesheet!$P$4,""),"")
AE21	AE21	=IF(Timesheet!Q22>0,CONCAT(Timesheet!$Q$5," ",Timesheet!$Q$4,""),"")
AF21	AF21	=IF(Timesheet!R22>0,CONCAT(Timesheet!$R$5," ",Timesheet!$R$4,""),"")
AG21	AG21	=IF(Timesheet!S22>0,CONCAT(Timesheet!$S$5," ",Timesheet!$S$4,""),"")
AH21	AH21	=IF(Timesheet!T22>0,CONCAT(Timesheet!$T$5," ",Timesheet!$T$4,""),"")
AI21	AI21	=IF(Timesheet!U22>0,CONCAT(Timesheet!$U$5," ",Timesheet!$U$4,""),"")
AJ21	AJ21	=IF(Timesheet!V22>0,CONCAT(Timesheet!$V$5," ",Timesheet!$V$4,""),"")
AK21	AK21	=IF(Timesheet!W22>0,CONCAT(Timesheet!$W$5," ",Timesheet!$W$4,""),"")
AL21	AL21	=IF(Timesheet!X22>0,CONCAT(Timesheet!$X$5," ",Timesheet!$X$4,""),"")
AM21	AM21	=IF(Timesheet!Y22>0,CONCAT(Timesheet!$Y$5," ",Timesheet!$Y$4,""),"")
AN21	AN21	=IF(Timesheet!Z22>0,CONCAT(Timesheet!$Z$5," ",Timesheet!$Z$4,""),"")
AO21	AO21	=IF(Timesheet!AA22>0,CONCAT(Timesheet!$AA$5," ",Timesheet!$AA$4,""),"")
AP21	AP21	=IF(Timesheet!AB22>0,CONCAT(Timesheet!$AB$5," ",Timesheet!$AB$4,""),"")
AQ21	AQ21	=IF(Timesheet!AC22>0,CONCAT(Timesheet!$AC$5," ",Timesheet!$AC$4,""),"")
AR21	AR21	=IF(Timesheet!AD22>0,CONCAT(Timesheet!$AD$5," ",Timesheet!$AD$4,""),"")
AS21	AS21	=IF(Timesheet!AE22>0,CONCAT(Timesheet!$AE$5," ",Timesheet!$AE$4,""),"")
AT21	AT21	=IF(Timesheet!AF22>0,CONCAT(Timesheet!$AF$5," ",Timesheet!$AF$4,""),"")
AU21	AU21	=IF(Timesheet!AG22>0,CONCAT(Timesheet!$AG$5," ",Timesheet!$AG$4,""),"")

DanteAmor · Jul 27, 2021

The detail is that in column A you have formulas and the macro looks for values.
Try the following:

VBA Code:

Sub AddUniqueValues()
  Dim a As Range, dic As Object, j As Long
  For Each a In Range("A2", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeFormulas, 23)
    Set dic = CreateObject("Scripting.Dictionary")
    For j = 4 To Cells(a.Row, Columns.Count).End(1).Column
      If Cells(a.Row, j) <> "" Then dic(Cells(a.Row, j).Value) = Empty
    Next
    a.Offset(dic.Count * -1, 3).Resize(dic.Count).Value = Application.Transpose(dic.keys)
  Next
End Sub

BraytonM · Jul 27, 2021

@DanteAmor Column D autofills with the correct values which is wonderful, but I am getting a run-time error '13': Type mismatch regarding the highlighted text in VBA. I have attached an image. If there is a way to fix this, that would be great, but if not, that is fine, too. Thank you.

DanteAmor · Jul 27, 2021

Check your data, this error is because in a cell in column A, let's say cell A56, you have data but in the columns of E to the right of row 56 they are empty, that is, there is nothing to add. To avoid the error, try the following:

VBA Code:

Sub AddUniqueValues()
  Dim a As Range, dic As Object, j As Long
  For Each a In Range("A2", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeFormulas, 23)
    Set dic = CreateObject("Scripting.Dictionary")
    For j = 5 To Cells(a.Row, Columns.Count).End(1).Column
      If Cells(a.Row, j) <> "" Then dic(Cells(a.Row, j).Value) = Empty
    Next
    On Error Resume Next
    a.Offset(dic.Count * -1, 3).Resize(dic.Count).Value = Application.Transpose(dic.keys)
    On Error GoTo 0
  Next
End Sub

BraytonM · Jul 27, 2021

DanteAmor said:
Check your data, this error is because in a cell in column A, let's say cell A56, you have data but in the columns of E to the right of row 56 they are empty, that is, there is nothing to add. To avoid the error, try the following:

VBA Code:

Sub AddUniqueValues() Dim a As Range, dic As Object, j As Long For Each a In Range("A2", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeFormulas, 23) Set dic = CreateObject("Scripting.Dictionary") For j = 5 To Cells(a.Row, Columns.Count).End(1).Column If Cells(a.Row, j) <> "" Then dic(Cells(a.Row, j).Value) = Empty Next On Error Resume Next a.Offset(dic.Count * -1, 3).Resize(dic.Count).Value = Application.Transpose(dic.keys) On Error GoTo 0 Next End Sub

Incredible! Thank you so much for all your help! This answered my original post and worked flawlessly. Again, thank you!

VBA for Adding Unique Values in Row to Column D

BraytonM

New Member

Attachments

Excel Facts

DanteAmor

Well-known Member

BraytonM

New Member

DanteAmor

Well-known Member

BraytonM

New Member

BraytonM

New Member

DanteAmor

Well-known Member

BraytonM

New Member

Attachments

DanteAmor

Well-known Member

BraytonM

New Member

Similar threads

Forum statistics

Share this page

VBA for Adding Unique Values in Row to Column D

New Member

Attachments

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Attachments

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock