compare columns align identical values

Microsoft Excel 2003 (full product)
January 24, 2011 at 00:22:41
Specs: Macintosh
coulm A contains a, b, c, d; column B contains a,c, d,e. the result should look like this:
a a
c c
d d

See More: compare columns align identical values

January 24, 2011 at 05:37:11
Is there a question in there someplace?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 24, 2011 at 12:11:03
Ooops, sorry, I oviously was a little too fast. I have two columns with partial identical values. I want to sort and align the colums in a way that values identical in both column are displayed in the same row, while values in either column that don't have an equivalence in the other column have a row of their own. (The "e" in the last row of my example belongs to column B.) So, my question is, whether this is possible at all, and, of course, how?

Report •

January 24, 2011 at 12:22:46
First, if you want to post data in this forum and keep the columns aligned, read the How To referenced in my signature line. If you follow the instructions given, your data should look like this:

     A   B
1    a   a
2    b
3    c   c
4    d   d
5        e

Second, you've shown us what you want as output, but we don't know what you started with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

January 24, 2011 at 12:37:01
Thank you for your patience. As I said, I started with to colums with partial identical values, like this:
  A B
1 a a
2 b c
3 c d
4 d e

And of course, Ms Excel.

Report •

January 24, 2011 at 20:44:44
Obviously, you have something more than the example that you posted or you would simply insert a cell at B2 and be finished.

I'll assume that your data set is slightly longer and more varied, maybe something closer to this:

   A    B
1  a	a
2  b	c
3  c	d
4  d	e
5  f	g
6  g	h
7  i	i
8  l	j
9	k

If that's the case, then try this code:

Option Explicit
Sub Isolate()
Dim lastA, lastB, shortCol, rw As Integer
'Determine short column so we know when to stop
    lastA = WorksheetFunction.CountA(Range("A:A"))
    lastB = WorksheetFunction.CountA(Range("B:B"))
       If lastA > lastB Then _
          shortCol = 2 Else shortCol = 1
'Set First Check Row
    rw = 1
'Check Column A against Column B, Row by Row
'Insert cell at non-matching data
  If Cells(rw, 1) <> "" And Cells(rw, 1) < Cells(rw, 2) Then
     Cells(rw, 2).Insert shift:=xlDown
     If Cells(rw, 2) <> "" And Cells(rw, 1) > Cells(rw, 2) Then
        Cells(rw, 1).Insert shift:=xlDown
     End If
  End If
'If there is nothing left to check in the Short Column, we're done
    If Cells(Rows.Count, shortCol).End(xlUp).Row + 1 = rw Then Exit Sub
'If not, increment Row counter and loop
    rw = rw + 1
 GoTo nxtChk
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 25, 2011 at 13:19:21
Well, of course my real list is, ehm, slightly longer ... this was only meant to be an example ...
Apart from that, I'm deeply impressed. That code works, even with my slightly longer list. It does exactly what I desperately tried to achievche for days, and wasn't able to. (To tell the truth, it didn't work on the first try, but then I realized that I had to make sure that all cells are formatted as text, and everything was fine.)
Thanks a lot, that was great support!
However, there is still an issue: how can I achieve that my heading row is left alone, i.e excluded from any sorting and aligning?
(And, if that doesn't sound kind of greedy, how has that code to be modified to do the same thing with 3 columns?)

Report •

January 25, 2011 at 21:28:28
re: Well, of course my real list is, ehm, slightly longer ... this was only meant to be an example ...

The thing is, it appears that you didn't get my point, as evidenced by your new requests.

You posted an example of a data set that did not have any column headers, just data in Row 1, so you received a solution that didn't deal with column headers.

You posted an example of data with 2 columns, so you received a solution that only dealt with 2 columns.

You might recall one of the things I said in Response 3 was "we don't know what you started with."

You responded with a 2 column example with no header row, when it now appears that that is not what you really have.

My only point here is that when asking for help in a forum such as this, "simplifying" the question is not always the right option. VBA code is very specific and often has to be written based on the exact layout described. Sometimes a modification required by new or additional requirements is easy, but often times an entire re-write of the code is required.

OK, I've put my soap box back in the closet, so let's get to your questions.

You'll have to deal the sorting part on your own since the code I offered doesn't sort the data, it just aligns it.

To prevent the header row (which I assume is in Row 1, even though you haven't said where it is) from being part of the alignment, change this:

'Set First Check Row
    rw = 1

to be this:

'Set First Check Row
    rw = 2

But as I inferred, I don't really know where your header row is, so that suggestion is just a guess.

As far as sorting 3 columns, once again, you'll have to give me an example of what your data looks like.

It may be obvious to you what your 3 columns look like, but I can't see your spreadsheet from where I'm sitting, so I'm not going to try and write any more code based on "assumptions". Working with 3 columns could be considerably more complicated, so I want to know exactly what I'm dealing with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 26, 2011 at 13:47:41
You are right - I shouldn't assume things to be obvious to others that seem obvious to me, and also I can see now why my example wasn't appropriate; however I believe that I would have said so if any other row than the first one was meant to be the header, because that wouldn't be obvious at all, even in my eyes...

Anyway, I'm perfectly happy with the solution you proposed, my starting point was indeed two rather long colums of names. And I very much appreciate not only the pace and the accuracy of your answers, but also you patience with my poorly drafted questions and exmaples.

My question about a third row means another turn of the screw, that's why I said, I don't want to sound greedy (as in: "you gave me one thing, now give me more"). But as things are evolving, I can imagine that sooner or later it could be useful to perform the same operation not only with 2, but with 3, or even 4 colums, each of which containing the same kind of data (alphanumeric strings, names) - that is, aligning them in a way that names that are identical in 2 or more columns appear in the same row, and that the respective cell remains empty, if a given name is not contained in a column. As you might have guessed, I'm no very famliar with Visual Basic (a fact which maybe also belongs to the "starting points"), but I try to learn, so any hint about how to modify the code is welcome. That ist, if it is just a question of modification...

Report •

March 1, 2011 at 08:30:06
After looking at the way the forum works, I guess I should have posted my request for assistance as a new discussion, otherwise, everyone thinks it is answered. Please disregard this post, as I will go ahead and post it in a new discussion. Sorry for the confusion.




I know this post was written over a month ago, however, your code looks like something that I could use myself. If I understand it correctly, it will take 2 columns of data, col A and B, and compare them to each other. If know match, it will shift cells down in one of the columns until there is a match. If this is true, then for some reason it isn't working on my data. I have two columns and both have a header. I made the header adjustment you mentioned to match what I have below:

'Set First Check Row
rw = 2

However, while I see things shifting down, not all the matches are aligning. I like this code because it doesn't change sort order which is critical to what I'm trying to do. I think it is because some of the things in my columns may not become a match until 3, 4 or even more rows down in one of the columns. Some how I need a code to look at both columns from the top down and when it sees the first thing in Col A that matches the first thing in Col B, shift all cells down in the column where the highest row match exist until the two like items are adjacent to each other. Below is an example of my data:

Component Part ++++++++++++++++++++++Component Part
5888-9800-195-1044 ++++++++++++++++++++++5888-9800-194-101
99154-7 +++++++++++++++++++++++++++++99154-6
994407-9 +++++++++++++++++++++++++++++994408-99
994408-99 +++++++++++++++++++++++++++++4401-1-199-101
4401-1-199-101 +++++++++++++++++++++++++++++99909-9
99909-9 +++++++++++++++++++++++++++++900-5-697-7
900-5-697-7 +++++++++++++++++++++++++++++AB51958-65
AB51958-65 +++++++++++++++++++++++++++++99909-1
99909-1 +++++++++++++++++++++++++++++9094-T44+AAB-QQ-A-950/4+.0644
D-4446-447 +++++++++++++++++++++++++++++DPX9MA-67P67P-444B-0001
DPX9MA-67P67P-444B-0001 ++++++++++++++++M99759/444-90-9
M99759/444-90-9 ++++++++++++++++++++++AB904465-440A
AB904465-440A ++++++++++++++++++++++AB445906-917
AB445906-917 +++++++++++++++++++++++++++++118-T-0449-69
118-T-0449-69 +++++++++++++++++++++++++++++118-T-0449-91
118-T-0449-91 +++++++++++++++++++++++++++++7055-T6+AAB-QQ-A-950/11+.0644

Any help would be greatly appreciated.



Report •

Ask Question