# Combining two columns in Excel into a “virtual helper column” using VBA

I have two columns that I combine into a third helper column by concatening them.

One is a 4-digit column, the other is single digit.

For instance:

COLUMNA COLUMNB COLUMNC 1234 1 12341 1234 1 12341 1234 2 12342 2345 1 23451 2345 2 23452 2345 2 23452

I then run an array formula in a 4th column (using an identifier that is in each row) that indicates how many unique values there are for column C for each identifier.

I would like to circumvent the use of the helper column if possible -- building a range (in numerical format) in VBA that I can reference in code so I can do away with the helper column.

Hopefully that makes sense to someone and they have an idea how to do it.

Thanks in advance.

EDIT: SORRY -- based on the very useful answer provided by Santosh below (which I can definitely use on another problem I have), I've realised that I haven't fully explained myself.

Each row has an identifier (shown here as COLUMN_IDENT)

COLUMNA COLUMNB COLUMNC COLUMN_IDENT 1234 1 12341 555 1234 1 12341 555 1234 2 12342 555 2345 1 23451 666 2345 2 23452 666 2345 2 23452 666

I then make a table that -- using an array formula (based on the FREQUENCY function) -- shows for each unique identifier (in this case 555 and 666) how many unique values occur for the concatenated COLUMN C. So, here it would 2 for 555 and also 2 for 666).

I end up with a report like this:

IDENT UNIQUE_COUNT 555 2 666 2

What I'm trying to do with code is to get rid of the need for the helper COLUMNC in the original table and still allow me to achieve the same result in the second table.

Again, thanks in advance.

## Answers

Try below code.

Sub sample() Dim lastRow As Long With Sheets("Sheet1") lastRow = .Range("A" & .Rows.Count).End(xlUp).Row .Range("A1:B" & lastRow).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes End With End Sub

OK, **months later** I've realised the answer to my own question.

You can do it a couple of ways (and there's probably more),

1) You can do it without VBA using an Array Formula.

So if you have:

+---+---------+---------+-----+ | | A | B | C | +---+---------+---------+-----+ | 1 | COLUMNA | COLUMNB | ID | | 2 | 1234 | 1 | 555 | | 3 | 1234 | 1 | 555 | | 4 | 1234 | 2 | 555 | | 5 | 2345 | 1 | 666 | | 6 | 2345 | 2 | 666 | | 7 | 2345 | 2 | 666 | | 8 | | | | +---+---------+---------+-----+

... and you want to generate this:

+---+-----+--------------+ | | D | E | +---+-----+--------------+ | 1 | ID | UNIQUE COUNT | | 2 | 555 | 2 | | 3 | 666 | 2 | | 4 | | | +---+-----+--------------+

... then put this as an Array Formula in E2 and E3 (making sure the ranges are correct):

=SUM(IF(FREQUENCY(IF(C2:C7=D2,MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0)),IF(C2:C7=D2,MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0)))>0,1))

2) If you need it in VBA, you can (amongst other things) do the Evaluate "cheat" (here I've used square brackets which is shorthand for Evaluate):

Range("E2") = [SUM(IF(FREQUENCY(IF(C2:C7=D2,MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0)),IF(C2:C7=D2,MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0)))>0,1))]

So, if anyone was wondering, that's a couple of ways of building "virtual helper columns".