VBA to make A=B and B=A in a database and clean it

jbesclapez

jbesclapez

Active Member
Joined
Feb 6, 2010
Messages
275
Hello Mister Excel,

How is Madam Excel? :rofl:
Sorry. :stickouttounge:

I have a problem with a list of data in column A and B.
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]


As you can see A = B = C = D and then D = Z and E = A so it means that A = B = C = D = E = Z
and then F = E so it means that A = B = C = D = E = Z = F
For G = H = I

I would like to have a simple table like that :

[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How can I do that in VBA?

Thanks for your help.
 
Sort by date Sort by votes
oldbrewer said:
I advise you not to try and make it dynamic until it does what you want on 10 rows
Click to expand...

Sorry but unfortunately it does not do what I want. I have hundreds of lines with these kind of equalities. And even with 10 lines, the macro is not doing the job needed. Please could you have a look at my post#9 with the examples. You will see that some lines are deleted. Basically in a database it is easier to read A = B ; A = C ; A = D ; A = E then A = B ; B= E ; D = B ; C = D . Even if it is the same, it is just a way to manage your data... Hope you see what i mean.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Code: 
Sub jb()
  Dim cell          As Range

  With Range("A2", Cells(Rows.Count, "B").End(xlUp))
    For Each cell In .Columns(2).Cells
      With cell
        If .Value2 < .Offset(, -1).Value2 Then
          .Cut
          .Offset(, -1).Insert
        End If
      End With
    Next cell

    .Sort Key1:=.Cells(1), Header:=xlNo
  End With
End Sub
 
Upvote 0
shg said:
Code: 
Sub jb()
  Dim cell          As Range

  With Range("A2", Cells(Rows.Count, "B").End(xlUp))
    For Each cell In .Columns(2).Cells
      With cell
        If .Value2 < .Offset(, -1).Value2 Then
          .Cut
          .Offset(, -1).Insert
        End If
      End With
    Next cell

    .Sort Key1:=.Cells(1), Header:=xlNo
  End With
End Sub
Click to expand...


Hello SHG and thanks for take time to answer my puzzle :-)

What you did is a lot better but it does not finish the job. Let me explain please.

Here is an example of data I work with:


<tbody>
[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q069R01 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q072R08 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q074R01 [/TD]

[TD="bgcolor: #A9D08E"] Q010R12 [/TD]
[TD="bgcolor: #A9D08E"] Q036R15 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q020R44 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q022R42 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q021R71 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q022R26 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q010R02 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q072R08 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q074R01 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q069R01 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q010R02 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q074R01 [/TD]

</tbody>

Your macro brings this back :


<tbody>
[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q069R01 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q072R08 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q074R01 [/TD]

[TD="bgcolor: #B4C6E7"] Q010R02 [/TD]
[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]

[TD="bgcolor: #FFFF00"] Q010R02 [/TD]
[TD="bgcolor: #FFFF00"] Q072R08 [/TD]

[TD="bgcolor: #A9D08E"] Q010R12 [/TD]
[TD="bgcolor: #A9D08E"] Q036R15 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q020R44 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q022R42 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q021R71 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q022R26 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q072R08 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q074R01 [/TD]

[TD="bgcolor: #FFFF00"] Q069R01 [/TD]
[TD="bgcolor: #FFFF00"] Q072R08 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q074R01 [/TD]

</tbody>

As you can see in the first line Q010R02 = Q069R01 and 2 lines from the end you can see Q069R01 = Q072R08 and in the last tine Q072R08 = Q074R01 so those 2 last equalities mean that Q010R02 = Q069R01 = Q072R08 = Q074R01 (there are 4 equality here). So this line of equality is the only one that we need ....
So if I tag the lines that are equals :


<tbody>
[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q069R01 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q072R08 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q074R01 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #A9D08E"] Q010R12 [/TD]
[TD="bgcolor: #A9D08E"] Q036R15 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q020R44 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q022R42 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q021R71 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q022R26 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q010R02 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q072R08 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

[TD="bgcolor: #B4C6E7"] Q069R01 [/TD]
[TD="bgcolor: #B4C6E7"] Q074R01 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q069R01 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q010R02 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

[TD="bgcolor: #FFFF00"] Q072R08 [/TD]
[TD="bgcolor: #FFFF00"] Q074R01 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] equals to Q010R02 [/TD]

</tbody>

The result expected should be like that :


<tbody>
[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q069R01 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q072R08 [/TD]

[TD="bgcolor: #DBDBDB"] Q010R02 [/TD]
[TD="bgcolor: #DBDBDB"] Q074R01 [/TD]

[TD="bgcolor: #A9D08E"] Q010R12 [/TD]
[TD="bgcolor: #A9D08E"] Q036R15 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q020R44 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] Q022R42 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q021R71 [/TD]

[TD="bgcolor: #FCE4D6"] Q021R34 [/TD]
[TD="bgcolor: #FCE4D6"] Q022R26 [/TD]

</tbody>
Basically all equalities are in only one "group". And as you can see this table a lot easier to read.
 
Upvote 0
Code: 
Sub jb()
  Dim cell          As Range
  Dim i             As Long
  Dim j             As Long

  With Range("A2", Cells(Rows.Count, "B").End(xlUp))
    For Each cell In .Columns(2).Cells
      With cell
        If .Value2 < .Offset(, -1).Value2 Then
          .Cut
          .Offset(, -1).Insert
        End If
      End With
    Next cell

    .Sort Key1:=.Cells(1), Key2:=.Cells(2), Header:=xlNo

    For i = 2 To .Rows.Count
      For j = 1 To i - 2
        If .Cells(i, "A").Value2 = .Cells(j, "B").Value2 Then
          .Cells(i, "A").Value2 = .Cells(j, "A").Value2
          Exit For
        End If
      Next j
    Next i
  
    .Sort Key1:=.Cells(1), Key2:=.Cells(2), Header:=xlNo
    .RemoveDuplicates Columns:=Array(1, 2)
  End With
End Sub
 
Upvote 0
Just noticed that

Code: 
      For j = 1 To i - 2

... should be

Code: 
      For j = 1 To i - 1
 
Upvote 0
shg said:
Just noticed that

Code: 
      For j = 1 To i - 2

... should be

Code: 
      For j = 1 To i - 1
Click to expand...


Good morning SHG!

I was away for a week... now monday morning and back to work :-)
I tested your VBA and it seems to be doing the job.
I will test it more on bigger "live" data but I wanted to thank you for the time and effort you put in this. It is greatly appreciated.

Please, one last thing, could you comment it because I do not really understand how you make it work. And especially the part where you organize the data...

Have a good day.
 
Last edited:
Upvote 0
jbesclapez said:
Good morning SHG!

I was away for a week... now monday morning and back to work :-)
I tested your VBA and it seems to be doing the job.
I will test it more on bigger "live" data but I wanted to thank you for the time and effort you put in this. It is greatly appreciated.

Please, one last thing, could you comment it because I do not really understand how you make it work. And especially the part where you organize the data...

Have a good day.
Click to expand...

Hi SHG!

Unfortunately I was a bit quick in saying "hurray" yesterday.
I worked on a massive list of data and they were things that were not behaving normally... I will give you an example.

Here is the table below.
Note the Q036R15 that is doubled.


<tbody>
[TD="bgcolor: #FFFFFF"] QR1Fev [/TD]
[TD="bgcolor: #FFFFFF"] QR2Fev [/TD]

[TD="bgcolor: #FFFFFF"] Q005R10 [/TD]
[TD="bgcolor: #FFFFFF"] Q036R15 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q069R01 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q072R08 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q074R01 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R12 [/TD]
[TD="bgcolor: #FFFFFF"] Q036R15 [/TD]

</tbody>

and therefore it should be grouped like this:


<tbody>
[TD="bgcolor: #FFFFFF"] QR1Fev [/TD]
[TD="bgcolor: #FFFFFF"] QR2Fev [/TD]

[TD="bgcolor: #FFFFFF"] Q005R10 [/TD]
[TD="bgcolor: #FFFFFF"] Q010R12 [/TD]

[TD="bgcolor: #FFFFFF"] Q005R10 [/TD]
[TD="bgcolor: #FFFFFF"] Q036R15 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q069R01 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q072R08 [/TD]

[TD="bgcolor: #FFFFFF"] Q010R02 [/TD]
[TD="bgcolor: #FFFFFF"] Q074R01 [/TD]

</tbody>

Please could you improve it that way?
 
Last edited:
Upvote 0
[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]table 2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]table 3[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]A[/TD]
[TD="class: xl22, width: 64"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]R[/TD]
[TD="class: xl22, width: 64"]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]#### > >[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]A[/TD]
[TD="class: xl22, width: 64"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]B[/TD]
[TD="class: xl22, width: 64"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]D[/TD]
[TD="class: xl22, width: 64"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]E[/TD]
[TD="class: xl22, width: 64"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]F[/TD]
[TD="class: xl22, width: 64"]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]G[/TD]
[TD="class: xl22, width: 64"]H[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD]I[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]G[/TD]
[TD="class: xl22, width: 64"]I[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]A[/TD]
[TD="class: xl22, width: 64"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]final step is to search if[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]a simple operation to put[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]sort by first column[/TD]
[TD][/TD]
[TD="colspan: 3"]left column letter of table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the lower letter first[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]then by second[/TD]
[TD][/TD]
[TD="colspan: 3"]occurs in right column of table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and remove duplicates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]if it does return the letter in left column[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ie A for the D match[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]the right column of table 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]is the same as the right column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]of table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#### > >[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 10"]=IF(ISERROR(OFFSET($J$1,MATCH(I3,$J$2:$J$10,0),-1)),I3,OFFSET($J$1,MATCH(I3,$J$2:$J$10,0),-1))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
388
metcala
M
S
  • Question Question
Layered return values in Excel sheet
Replies
4
Views
89
lrobbo314
lrobbo314
K
  • Question Question
conditional formatting question
Replies
2
Views
564
kerm007
K
A
  • Solved
Return Records if a match is found
Replies
4
Views
162
agentkramr
A
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
48
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top