Read data from 1 column and relate to data in SAME column

[jex57]

Hi All,
Is there a way to read data in Column A, match it to other data in Column A and return all the values in Column B where the data is matched:

For example, in column A I have acquirer appearing with mas_stgmastercardtransactionacquirer_dly AND mas_stgmastercardtransactionissuer_dly, what I need to see is Column C with mas_stgmastercardtransactionacquirer_dly and Column D with mas_stgmastercardtransactionissuer_dly etc - so if there with 5 matches, it would go to column E, F and G as well.
Is there anyway to do this?

Thankyou

 

[FatBoyClam]

Are you looking for the results to be produced into a new tab?

Are you looking for the results to be populated into the columns C-n on the same tab, removing the rows with duplicates in Column A?

Are you looking for the results to be populated into the columns C-n on the same tab, keeping the rows with duplicates in Column A?

Are you looking for a formula only solution, or is VBA OK?

 

[jex57]

Are you looking for the results to be produced into a new tab?

Are you looking for the results to be populated into the columns C-n on the same tab, removing the rows with duplicates in Column A?

Are you looking for the results to be populated into the columns C-n on the same tab, keeping the rows with duplicates in Column A?

Are you looking for a formula only solution, or is VBA OK?

Thank you
A) results to be in new tab

Population should be something like this

Am looking for the VBA - dont think a formula can work

Thank you

 

[FatBoyClam]

OK, this should do the trick.

Assumptions:
Source data is on "Sheet1", results will go on "Sheet2"
Source list has no headers, row 1 contains data.
Sheet2 is empty to start.

If these assumptions are not correct, you'll need to adjust the code accordingly.

Sub PivotList()

Dim rngSource As Range, rngDest As Range

Set rngSource = Sheets("Sheet1").Range("A1")

Do Until rngSource.Value = ""

    Set rngDest = Sheets("Sheet2").Range("A:A")
    
    Set rngDest = rngDest.Find(What:=rngSource.Value, LookIn:=xlValues, LookAt:=xlWhole)
    
    If rngDest Is Nothing Then
        Set rngDest = Sheets("Sheet2").Range("A1")
        If rngDest.Value <> "" Then
            If rngDest.Offset(1, 0) <> "" Then
                Set rngDest = rngDest.End(xlDown)
            End If
            Set rngDest = rngDest.Offset(1, 0)
        End If
        rngDest.Value = rngSource.Value
    End If
    
    Do Until rngDest.Value = ""
    
        Set rngDest = rngDest.Offset(0, 1)
    
    Loop
    
    rngDest.Value = rngSource.Offset(0, 1).Value
        
    Set rngSource = rngSource.Offset(1, 0)
Loop


End Sub