need to compare two columns in excel and bring multiple reference into single cell

Question

I am looking for an excel formula for the below requirement:

I have two sheets, in which sheet1 contains the below data:

Column1 contains: R1, R1, R1, R2, R2, R3, R3, R4 Column2 contains: T1, T2, T3, T4, T5, T6, T7, T8

In the second sheet contains the below data:

Column1 Contains: R1, R2, R3, R4 Now in Column2 i am expecting the output as T1, T2, T3 for R1 T4, T5 for R2 T6, T7 for R3 T8 for R4

Note: I would need to put T1, T2, T3 in single cell. Like wise for other matching values.

Currently I am using the below formula:

=INDEX('Sheet'!1A3:A100, MATCH(A3, 'Sheet2'!A3:A100, 0)) For the above formale i am getting the out put as:

Column1: R1, R2 Column2: T1, T2

Thanks for the support!

5202456 5202456 · Accepted Answer · 2016-10-04T14:08:01

VBA code will do what you require, here is an example that will get the results you are after. A couple of things to point out:

The columns do not have headings

Sheets are named Sheet1 and Sheet2

The data in Sheet1 Column A needs to be in Ascending or descending order as this macro will not work if the values to be processed are not grouped together.

Amend as required:

Sub ExampleMacro()

Dim LastRow As Integer
Dim CurrentRow As Integer
Dim i As Integer
Dim RefResult

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

CurrentRow = 1
For i = 1 To LastRow

If Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & CurrentRow).Value Then    
Sheets("Sheet2").Range("B1").Value = RefResult 
Else

If Sheets("Sheet2").Range("A1").Value <> Sheets("Sheet1").Range("A" & CurrentRow).Value Then
RefResult = Sheets("Sheet1").Range("B" & CurrentRow).Value
Sheets("Sheet2").Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & CurrentRow).Value    
Sheets("Sheet2").Range("B1").Value = RefResult    
Else
End If

End If

CurrentRow = CurrentRow + 1
RefResult = RefResult & ", " & Sheets("Sheet1").Range("B" & CurrentRow).Value

Next I

End Sub

need to compare two columns in excel and bring multiple reference into single cell

1 Answers