Align Rows - Compare Columns (Issue with shg's alignkeys macro)

[Massael]

Hi All,

I have a spreadsheet with four columns of data. I'm hoping to align the data in columns AB with CD using A and C as the "keys". I found shg's alignkeys macro and have been trying to use that. Unfortunately, Excel is locking up on me.

Here's a snippet example:

2VB3BmI.png

Any ideas?

Thanks!

[xladept]

Hi Massael,

I wish that you had posted a spreadsheet sample - this is untested, if it works don't blink

Please Login or Register  to view this content.

[Massael]

Thanks xladept - it gives me this error:

Run-time error 9, subscript out of range.

It highlights this row:


If A(i, 1) = "" And C(j, 1) = "" Then GoTo PostAC

[Massael]

Here's the actual file in zip format:

http://sdrv.ms/18INgAp

[xladept]

Hi Massael,

Try this now:

Please Login or Register  to view this content.

[Massael]

Thanks! This worked great!

Is it possible to write a simple formula that will compare the values in column B and D within a threshold (say 10%) and mark the ones that aren't within that threshold?

[xladept]

You're welcome! Yes, we could color the outliers but it would take another pass.

[xladept]

Hi Massael,

This will color the outliers red as well as the records with zero as a sum - it can only be run once,not twice and that's been driving me crazy - let me know if you like it:

Please Login or Register  to view this content.

[Massael]

Awesome. Thank you so very much for your help!

[xladept]

Hi Massael,

My Internet was down for two days as I changed ISP's. I fancied up the program a little more and made it so that you can run it over and over if you want - The columns A and C were in different formats:

Please Login or Register  to view this content.

[Massael]

Thank you for the updated code. I actually just tried to re-run this with a new dataset and received the same error code as before, Run-time error 9.

I've attached the file in case it's an issue with the file itself. https://dl.dropboxusercontent.com/u/...ata-macro.xlsm

[xladept]

Hi Massael,

We've got overflow - try this:

Please Login or Register  to view this content.

[Massael]

That worked great, thank you!

[xladept]

Wonderful!

[Massael]

Apparently I exist to confound you - so once again I am going to ask for a little help!

It seems that the data is not aligning as expected when I run the macro. I've included the file with macro. I would really appreciate it if you could take a look.

https://dl.dropboxusercontent.com/u/...data-help.xlsm

[jindon]

Seems no matched row in your file.

Try this

Please Login or Register  to view this content.

[xladept]

Hi Massael,

Jindon writes very good code but I think the problem may be in the data - we've got to "scrub" it somehow, but I haven't yet figured it out

[xladept]

Hi Massael,

This is what I'm trying:

Please Login or Register  to view this content.

(I figured it out)

[Massael]

xladept,

It seems some data wasn't being handled (spaces, leading zeroes), so I scrubbed those. Now the macro doesn't work.

Here's the file after cleaning the data up:

https://dl.dropboxusercontent.com/u/...data-help.xlsm

[Massael]

Seems no matched row in your file.

Try this

Please Login or Register  to view this content.

jindon,

Your code runs after I cleaned the data up and finds matches, however I lose a lot of data in the C,D columns. Any idea why?

[xladept]

Hi Massael,

Did you incorporate my scrub program as in the last posting - it ran nicely for me??

Also, it is paramount that this line read like this:

Please Login or Register  to view this content.

[xladept]

Hi Massael,

I am confounded - I've asked other members for help on this.

[jindon]

jindon,

Your code runs after I cleaned the data up and finds matches, however I lose a lot of data in the C,D columns. Any idea why?

Have you checked col.C,D further down?

Unmatched data will line up below the end of col.A,B.

Otherwise, need to see your cleaned up file.

[Massael]

Have you checked col.C,D further down?

Unmatched data will line up below the end of col.A,B.

Otherwise, need to see your cleaned up file.

Jindon,

It seems to reduce the record count from 58,000 in Col C,D down to 22,000.

Original data pre-macro:
https://dl.dropboxusercontent.com/u/...data-help.xlsm

Here's the file post macro:
https://dl.dropboxusercontent.com/u/...ata-help2.xlsm

[Massael]

xladept,

Thank you - I realize this is strange. I'm not sure why the overflow is occurring. As i'm relatively familiar with scrubbing data, I did that myself, so it's now clear. After doing that - suddenly the macro no longer runs. Sorry this is so troublesome, I truly appreciate your help.

[jindon]

I just run the code on the file you last provided and the amount of the rows are the same.

Before
Col.A has 58683 rows
Col.C has 59749 rows

and the result has the same with matched rows are lined up and end up with 82238 rows.

P.S
And the result file you attached was not from my code.

[Massael]

I'm not seeing that result - wondering why my macro is working differently...

The second file I attached is after running your macro, and I have 21229 rows in columns C&D. I'm running Excel 2013 if that makes a difference. Is there a special method to executing the macro?

[Massael]

To note, we know there are a few thousand that don't match - ~6-10,000 - but not 30,000+

[jindon]

OOOps, terribly sorry, I think I gave you the one before I have edited.

The last line should read as

Please Login or Register  to view this content.

[Massael]

The data is all there now, however not matching properly. Example value:

120062001

Is in cells C58777 and also cell A101 but not aligned. Any idea why? I really appreciate the help.

[Massael]

Looking at this more, seems it's a Text/Number issue as there's alphanumeric data. Any way to account for this?

[jindon]

OK, that's because of the difference of data types.
Try this one

Please Login or Register  to view this content.

[Massael]

Perfect! Thank you!!!!

[jindon]

Glad it worked.

Can you mark this thread as "Solved"?

[xladept]

Hi Massael,

I'm still confounded but I've added that percentage routine to Jindon's code:

Please Login or Register  to view this content.