Matching numbers in multiple columns and aligning them in the same rows

**DANNY DUNPHY** · 10-01-2012, 12:20 PM

Hi,

I am copying 2 columns of numbers from different sources and want to align them in the same rows.
The 2 columns have to be aligned with 2 other columns of numbers in the same row. Any two
number sequence in the first 2 columns will not be the same as any other sequence in these columns
and this also applies to the other 2 columns. The 2 number sequence in the first 2 columns will match 2
similar numbers in the next 2 columns.
I have attached a sample. The columns that need matching and aligning are AD and HK. Extra columns can
used to align the numbers in the rows. I can then hide columns HK or whatever.

Danny.

**stanleydgromjr** · 10-02-2012, 12:16 AM

DANNY DUNPHY,

Detach/open workbook AlignLists Plus - DANNY DUNPHY - EF864750 - SDG17.xlsm and run the AlignLists macro.

If you want to use the macro on another workbook:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the AlignLists macro.

**DANNY DUNPHY** · 10-02-2012, 02:15 PM

Hi Stanley,
This macro worked fine.
Thank you very much.
Did you write the macro or was it a series of recorded commands. The reason I ask I was hoping to insert this to a different workbook.
The data would not be in the same start position, ie A2. When I entered data starting at A4 instead of A2, This macro did not work.
I also have a 3rd group of columns (similar to the first 2 groups) that have to be aligned to the first 2 groups. I left this out initially
because I thought it would easier to explain what I was seeking to accomplish. I was hoping then to continue on myself.
Thank you again for your assistance. It is much appreciated.

Danny.

**stanleydgromjr** · 10-02-2012, 03:51 PM

DANNY DUNPHY,

Thanks for the feedback.

You are very welcome. Glad I could help.

Did you write the macro or was it a series of recorded commands.

The macro is an offspring from a smaller version (created by someone else) that aligns numbers in column A and B.

The reason I ask I was hoping to insert this to a different workbook.

If the structure of the raw data is different, then I will have to see the the workbook/worksheet with the new information.

Please attach the new workbook.

**DANNY DUNPHY** · 10-03-2012, 01:39 PM

Hi Stan,

I have attached a file with the 3rd group of columns.
I need to match columns OR with AD and HK.
I have changed the name of the worksheet "sheet1" to "data entry" in your macro.
The macro worked with the name change. This is the name of the sheet in my workbook.
I can transpose the data after the macro has aligned the data.

**stanleydgromjr** · 10-03-2012, 04:01 PM

DANNY DUNPHY,

I am not able to copy your raw data worksheet into another worksheet within the same workbook?????

I also tried to just copy the columns that I need for testing. Still no good?????

**DANNY DUNPHY** · 10-03-2012, 04:33 PM

Hi Stan,
I pasted the raw data directly into a workbook, to a worksheet called "data entry".
I inserted your macro into the workbook and it worked for me.
I changed the name of the worksheet "sheet1" to "data entry" in your macro, so it would recognize
the worksheet. I then auto copied the data to another worksheet within the same workbook.
I have attached the workbook you sent me,with the 3 group of columns added in.
Hopefully you may have better luck with this workbook.
Thank you once again for your assistance.
AlignLists Plus - DANNY DUNPHY - EF864750 - SDG17.xlsm

Regards,
Danny.

**stanleydgromjr** · 10-05-2012, 03:30 PM

DANNY DUNPHY,

The method I used to align the two original columns works well.

I am having a problem trying to adjust the macro for three columns.

But, I have some code that looks very promising working with two or more columns.

Be back soon.

**stanleydgromjr** · 10-06-2012, 06:44 AM

DANNY DUNPHY,

I am not having any luck with your new dataset.

I will keep working on the issue.

In the meantime, in the Quick Reply box, just put the word BUMP. Then, click on the Post Reply button, and someone else will assist you.

**DANNY DUNPHY** · 10-07-2012, 06:50 AM

Bump

rgds,
danny

**stanleydgromjr** · 10-07-2012, 08:03 PM

DANNY DUNPHY,

Does your latest workbook, worksheet Data Entry, contain Conditional Formatting in columns A, B, or C?

**stanleydgromjr** · 10-07-2012, 10:05 PM

DANNY DUNPHY,

With your raw data in worksheet Data Entry, with all columns raw data beginning in row 2.

Detach/open workbook AlignLists Plus3 - DANNY DUNPHY - EF864750 - SDG17.xlsm and run the AlignListsAHO macro.

If you want to use the macro on another workbook:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the AlignListsAHO macro.

**DANNY DUNPHY** · 10-08-2012, 10:26 AM

No. I have conditional formatting in the woksheet but not in A B or C. You can use as many columns as you like. I can build or move my formulae and formatting around them afterwards.

Thanks for your persistance.
Danny.

**stanleydgromjr** · 10-08-2012, 02:18 PM

DANNY DUNPHY,

Have you tried my latest attached workbook (Reply #12)?

**DANNY DUNPHY** · 10-08-2012, 06:49 PM

Hi Stan,
It didn't work for me first time. I deleted columns A to U in my workbook just in case they weren't completely empty. I inserted columns A to U back in again to make space for the 3 sets of data being pasted in. I am using formulae etc from V on. I copied the file to. xlsm also. I tried it again and it worked. I got the following in columns A1 & AL though. This isn't a problem: I can hide these columns. I would like to try it a few more times with different data in the same format to see what happens. The data I entered was different from the data sent to you. I'll let you know how I get on. Have to go now.
15588558.001
15588558.002
15588558.003
15588558.004
15588558.005.....and so on.

Regards,
Danny.

**stanleydgromjr** · 10-08-2012, 07:34 PM

DANNY DUNPHY,

Those numbers in column A were used as an index for aligning the three other groups of column data. When the macro ends, it removes the 4 additional columns I inserted to do the alignment.

The data I entered was different from the data sent to you.

With your raw data in worksheet Data Entry, with all columns raw data beginning in row 2.

And, the structure of each of the three groups, containing the correct number of columns, must match the last workbook I attached.

If you change any of the original column groups, then the macro will probably not work correctly.

Matching numbers in multiple columns and aligning them in the same rows

LinkBack

Thread Tools

Rate This Thread

Display

Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Re: Matching numbers in multiple columns and aligning them in the same rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions