Excel 2007 : Trying to match like items in multiple columns

**Cshadwick07** · 06-14-2012, 11:01 AM

I need to take column C and match to column B so that they are aligned but I also need to have columns D-G follow column C.

10386 AMRA309-20 AMR A315-20 C-HEAVY DUTY ADHESIVE 12/20 OZ 9.89 74.85 6/15/2012
10386 AMRA313-20 AMR A328-16 GLIDE SILICONE LUBRIC12/16OZ 13.28 71.24 6/15/2012
10386 AMRA314-20 AMR A361-12 ADVANCED ELECTRONICSDUSTER 9.57 115.19 6/15/2012
10386 AMRA315-20 AMR A363-16 CONTACT & CIRCUIT BOACLEANER 12/16 23.92 190.55 6/15/2012

can this be done with a formula or do I have to do it manually?

**NBVC** · 06-14-2012, 11:27 AM

Not sure if there is a space in column 3 where column B doesn't have one...

But assuming that there is that difference, and that the codes are consistent (i.e. 3 chars, space, 7 chars)... then try in new column:

=INDEX(C:C,MATCH(LEFT($B2,3)&" "&RIGHT($B2,7),$C:$C,0))

copied down and across.

You can then copy these and paste special >> Values over original column D to G and delete the formula columns...

**Cshadwick07** · 06-14-2012, 11:52 AM

Ok, that is awesome but I have one more question. what if the second set of characters are more or less than 7? they range from 4-10.

**NBVC** · 06-14-2012, 12:03 PM

Try:

=INDEX(C:C,MATCH(LEFT($B2,3)&" "&MID($B2,4,100),$C:$C,0))

**Cshadwick07** · 06-14-2012, 01:21 PM

Thank you very much for all your help.

Excel 2007 : Trying to match like items in multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

Trying to match like items in multiple columns

Re: Trying to match like items in multiple columns

Re: Trying to match like items in multiple columns

Re: Trying to match like items in multiple columns

Re: Trying to match like items in multiple columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions