Match values from two different columns and sort according to the matched values

[kongfookann]

Hi everyone,

I am Kong from Malaysia. I would like to ask, if I have the following data:

Column A Column B
1111 0
1112 0
1113 1111
1114 1112
1115 1111
1116 1118

How do I sort the data so that if the value of Column B is identical with a value in Column A, it will appear below the row of the identical number in Column A. For example: (If value in column B is "1111", its row appears below the row in which "1111" appears in Column A)

Column A Column B
1111 0
1113 1111
1115 1111
1112 0
1114 1112
1116 1118

Appreciate everyone who could help.

With thanks,
Kong - Malaysia

[:) Sixthsense :)]

Just select both Column-A & Column-B data and apply the sorting

[kongfookann]

Hi Sixthsense,

Thank you for your response on my inquiry. If I were to "sort" both columns, it will only appear in a certain order. But I wanted the matched value in Column B to be right below the identical number in Column A. Perhaps the attachment below may help in your understanding of my predicament.

Anyhow, thanks a lot for your initial explanation!

Cheers
Kong

[ragulduy]

No idea how to do it as a formula but I think this macro does it:

Please Login or Register  to view this content.

[Olly]

A formula option: add this to a helper column (e.g. Column C) and sort on this column:

Please Login or Register  to view this content.

[kongfookann]

Dear Yudlugar,

Appreciate your response on this matter with a macro. May I ask what does this line of code means:

For count1 = count + 2 To Range("A" & Rows.count).End(xlUp).Row

What does "&" means in the () of Range function?

Thanks if you can clarify it to help me understand better.

Cheers
Kong

No idea how to do it as a formula but I think this macro does it:

Please Login or Register  to view this content.

[kongfookann]

OllyXLS,

Thank you for the formula. It works well on my data.

Cheers
Kong

A formula option: add this to a helper column (e.g. Column C) and sort on this column:

Please Login or Register  to view this content.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[ragulduy]

The & is the how you tie a string and a variable or property together.

So rows.count is the number of rows in the spreadsheet
"A" & rows.count
would return A65536 for me, because I have 65536 rows in excel.
The whole line is a way of finding the last used cell in column A.

[kongfookann]

Dear Yudlugar,

I did some editing to the your earlier macro as shown below:

Please Login or Register  to view this content.

Basically, what I intended to do is instead of just shifting Column A and Column B in your code, I wanted to make it to shift the entire row. That is why for the range, I included "Z" as value 2 ( Range("a" & count1, "z" & count1).Cut). However, if you have the code in visual basic to shift the entire row,appreciate if you could let me know.

Secondly, referring to the attached excel, how do I make all the identical numbers in column A to be grouped together? You can see I try to use this code but it still will not work:

Please Login or Register  to view this content.

Appreciate your advice on this matter.

Regards
Kong

[ragulduy]

I believe you can do:

Please Login or Register  to view this content.

although you might need Range("A" & count2+1) instead of rows(Count2+1)...

Alternatively, you can use .entirerow to return a whole row:

Please Login or Register  to view this content.

[alansidman]

@kongfookann

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them for you today. Please read all Forum Rules and comply in the future.)