Need an Advance Sorting code/VBA.

[nobodyNZ]

Hi Guys,
My data has about 500 rows from Column I to R (total 10 columns).
The data in row N & O are duplicated.. And row O data can be Self-Duplicated
Now I need to sort:

I 1 9 15

J APPLE Sony Samsung

K Each Set Set

L Stock Stock Stock

M AB AC DA

N 375-9323 384-803 790-3268

O 375-9323 384-803 375-9323 790-3268

P 2 15 6 8 10

Q pсs. pсs. pсs. pсs. pсs.

R A NO D D D

1. If data in Column N = Column O (Duplicate), Move all data from "Columns I J K L M N" to that row.
2. If data in Column O is Self-Duplicated. Then copy & Move all data from "Columns I J K L M N" to that row.
3. If data in Column O is empty or contains something that doesn't exist on Column N. Then all data from "Columns I J K L M N" should be empty.

Results should be like this:

I 1 9 1 15

J APPLE Sony APPLE Samsung

K Each Set Each Set

L Stock Stock Stock Stock

M AB AC AB DA

N 375-9323 384-803 375-9323 790-3268

O 375-9323 384-803 375-9323 790-3268

P 2 15 6 8 10

Q pсs. pсs. pсs. pсs. pсs.

R A NO D D D

Honestly speaking, I've spent this whole weekend for this but I'm getting no where as I don't have much VBA background and My head has been hurting trying to solve this.
So, I'm wondering If someone here is kind enough to write an example coding/VBA for this.

Thanks guys,

[Kaper]

Of course it can be done with macro, but with such data I think you could use MATCH function to locate value from O column in column N
and then INDEX to return values from columns I:M
Have you considered using formula too?

[nobodyNZ]

Hi there,
I'm currently using the below VBA coding which is working fine for less than 200 rows. However, when applying it to 500+ rows the PC takes very long time to process (8-10mins) and quite often it get stucked.
So I think there must be a better coding than this. Thanks for your advice on the INDEX & MATCH Function. I've been looking up threads & posts to understand how these works seperately & in combination. But it will take quite a lot of time as I don't have much VBA background. Just started to do VBA coding for less than a week. So, I would appreciate any helps from the Experts.
Thanks Guys,

Please Login or Register  to view this content.

[xladept]

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

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] 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



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[nilem]

Hi nobodyNZ,
Could you please attach a sample file in which your code works for 8-10 minutes

[nobodyNZ]

Hi Guys,
Problem has been solved with Function Index.Match.Match.
Thanks for your advices !!

[xladept]

Hi nobododyNZ,

Glad you got it - but I wrote this so you may as well have it:

Please Login or Register  to view this content.

BTW: - You should mark this thread solved.