Can a macro recognize a number and then preform 2 tasks?

**Nemo5** · 08-24-2009, 08:30 PM

Over the weekend I had to look at 220 strings of numbers, some strings with as much as 20 numbers and determine if the numbers represented red, green, blue, or yellow and how many of each color from a parent list. I did it all by hand. After getting help here from JBeaucaire on my tally sheet, which I successfully recreated with their guidance, I thought I might ask this question. How would one go about creating a macro that would when it seen a certain number, it would put the color in the column immediately to the left, and the number of colors in the column immediately to the left. I am new to excel so I don't know if this is even possible. Thanks for any guidance someone may provide.

**DonkeyOte** · 08-25-2009, 02:00 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

**Nemo5** · 08-25-2009, 03:26 AM

Here is an example of what I was wondering the outcome could be.

**DonkeyOte** · 08-25-2009, 03:43 AM

There's not really anything to go on from here I'm afraid... what does each column represent, how are the various values to be determined etc ... it's very hard to ever offer too much information.

**JBeaucaire** · 08-25-2009, 10:42 AM

Be sure to post examples of BEFORE/AFTER so everyone can see where you started and where you ended. Only way we can know what's going on here.

**Nemo5** · 08-25-2009, 06:32 PM

I am uploading a new workbook to hopefully explain what I would like to do. Sheet 1 has 12 cartons listed. When I am done making list there will be 500. Sheet 2 has carton with what part is in it but not how many of each part or color. This is an example. There will actually be around 20 cartons per sheet. Sheet 3 is what I would like to have happen. From the information on sheet 1, is there a simple macro that would populate the parts and color column of sheet 2 to look like sheet 3.

**JBeaucaire** · 08-25-2009, 06:48 PM

This is a normal INDEX/MATCH approach:

A2:
=INDEX(Sheet1!$B:$B, MATCH(B2, Sheet1!$A:$A, 0))
C2:
=INDEX(Sheet1!$C:$C, MATCH(B2, Sheet1!$A:$A, 0))

Copy down, then copy each over the other set.