Identify matching data in different rows and put into same row in new worksheet

[Sal1]

This is the procedure I want to create. I also attached a workbook that demonstrates what I am trying to accomplish. The code I am using is posted below as well as the problems I am having with it.

1. Go to Sheet “Original” look at the data in cell A1 then look for a match of that data in the rows of Column C, Column F, Column H, Column J, Column L, Column N, and Column P

2. Once matches for Column A are found in the different rows of Column C, Column F, Column H, Column J, Column L, Column N, and Column P then copy the matching row of Column A:B, Column C:E, Column F:G, Column H:I, Column J:K, Column L:M, Column N:O, and Column P:R

3. Next paste the different rows from Column A:B, Column C:E, Column F:G, Column H:I, Column J:K, Column L:M, Column N:O, and Column P:R into Sheet “Matched” so they are in the same row instead of different rows.

4. Repeat the process for every row in Column A

5. Carry over any extra rows with data from Column A:B, Column C:E, Column F:G, Column H:I, Column J:K, Column L:M, Column N:O, and Column P:R that did not have matches in Column A, Column C, Column F, Column H, Column J, Column L, Column N, and Column P.

Please Login or Register  to view this content.

The macro is grabbing A:B, C:E, and P:R and putting them into the same row in Sheet Matched, but the code is missing everything else I would like to see happen. Can you help me figure out what I need to change in this macro in order to make it work? See attached workbook. Thank you for your help.

[Sal1]

Hi All, just wondering if anyone had any ideas on how to fix this? Thanks for your time.

[JBeaucaire]

Here's how I would write that:

Please Login or Register  to view this content.

[Sal1]

Thanks Man! I appreciate your kindness to help me. Maybe this is my error. When I entered the code into the workbook that is attached to this thread and ran it I received an error message that said 'No cells were found' and this line of the code was highlighted

Please Login or Register  to view this content.

I am not sure what it could be, do you know?

[ajaykgarg]

I think you get this error when no SpecialCells are found.
This may help:-

Please Login or Register  to view this content.

hth
Ajay

[JBeaucaire]

That's a concern if you tried on the posted file. Did you insert the macro into a standard module? (Insert > Module)

The sample file has constants in column A so the RNG should set properly.

If you continue to get the error, post your file again with the macro installed and the workbook misbehaving so we can look at it together.

[Sal1]

ajaykgarg this works very well; thank you for your help!

[Sal1]

JBeaucaire; you are correct the way I entered the macro made the difference. Once I did it the way you instructed me to do everything worked perfectly. Thank you for your patience and help; I appreciate it very much!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Sal1]

Hi JBeaucaire,

Thank you again. I just realized the macro will not work if the data in sheet ‘Original’ is not color coded. I set up the workbook that way to make it easier to understand my objective however I did not specify the data would not be color coded in the instructions; my apologies. Is it possible to modify this macro so it still works if the data in sheet ‘Original’ is not color coded? Thank you for your help!

Please Login or Register  to view this content.

[JBeaucaire]

My macro isn't doing anything with color coding. It functions strictly off of the values in the cells.

If you're experiencing specific difficulty, upload a new exemplary workbook.

[Sal1]

Thank you JBeaucaire. I reattached the workbook here. You are very kind! Just to give you an idea. Perhaps my error but when I remove the colors

Please Login or Register  to view this content.

this part of the code is highlighted. If I add

Please Login or Register  to view this content.

then this part of the code

Please Login or Register  to view this content.

gets highlighted. Also if I leave the colors alone the code works once but if I delete everything from Sheet 'Matched' and try to use the code again this part

Please Login or Register  to view this content.

gets highlighted in yellow with an error message. This workbook is different from the original because I removed the colors and added dashes to some of the data; I have the same problems without dashes. Any input is greatly appreciated.

[JBeaucaire]

I had similar problem until I retyped your sheetname, no idea why, they looked the same to me.

I also removed all the extra baggage on the MATCHED sheet for now so the macro can display in its pure form, with only a series of values in column A to match.

My version of your workbook has the macro in it, a couple of small tweaks.

[Sal1]

Wow! This is awesome. I noticed if some data is not in Column A on sheet 'Matched' but is in a Column on sheet 'Original' the data will not come over to sheet 'Matched'. Is there a way to get the remaining data from the same column of 'Original' over to the same column of "Matched' even though it does not have a match in Column A?

[JBeaucaire]

Not offhand. That would be a whole new process.

Mockup what the organized final output would look like for the unmatched values.

[Sal1]

Thank you JBeaucaire; I appreciate your help!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED .