Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positions

**pato225** · 03-11-2015, 11:47 PM

Hi All, I am trying to search values in column labeled "CPT1" that exists in column labeled "CPT2", if found return the values in column labeled "CPT Values Found" while maintaining the relative positions or matching the positions as those in column labeled "CPT1". The values that do not exist will be called "NF". Also append the respective values of column labeled "MOD2" in column labeled "Mode Values". Please see the data before running macro and after running macro to get my idea clear. Thanks guys, I haven't been here for a while and now losing my vba knowledge.

Before Macro Run
Before Macro Run.png

After Macro Ran
After Macro Ran.png

**FDibbins** · 03-12-2015, 12:05 AM

Why do you need a macro for this? =IF(ISERROR(VLOOUP(C2,$A$2:$B$10,1,0),"NF",VLOOUP(C2,$A$2:$B$10,1,0)) will probably do this for you

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**pato225** · 03-12-2015, 12:08 AM

Attached is an Excel File for ease

**pato225** · 03-12-2015, 12:10 AM

okay I just did, its going to get more complex I just need a start and will expand the idea from there

**:) Sixthsense :)** · 03-12-2015, 12:12 AM

Please Login or Register  to view this content.

**FDibbins** · 03-12-2015, 12:16 AM

Put this in J3 and copy down...
=IF(ISERROR(VLOOKUP($C3,$A$3:$B$11,1,0)),"NF",VLOOKUP($C3,$A$3:$B$11,1,0))
and this in K3, copied down...
=IF(ISERROR(VLOOKUP($C3,$A$3:$B$11,2,0)),"",IF(VLOOKUP($C3,$A$3:$B$11,2,0)="","",VLOOKUP($C3,$A$3:$B$11,2,0)))

**pato225** · 03-12-2015, 01:35 AM

Thank you FDibbins and Sixthsense, there are a lot of moving targets that I haven't mentioned. Please look at this excel named "Vba_AutoMatch_Retain Positions". There are variables I want to account for, for example in column "F" (CPT2) 0042T has 2 duplicates and column J (Mod2) has corresponding values that indicates that 0042T can be by itself, or a value of 80 or a value of 82. So I was thinking I could add columns G (Found CPT1 Values in CPT2) where the search values of columns A (CPT1) are returned, append their corresponding values in column B (Mod1) into column H (Append Corresponding Mod1) before I even start looking at column C to start another comparison. I guess my other question is what happens when there is only one value of 0042T in column A and 3 values in column F? So I am thinking of what kind of return I will need in these case. So that's why I am saying it is getting a little complex even difficult for me to explain. But essentially I just want to search the first column (column A) and if they exist in Column F, then I will first append their corresponding values in column B while maintaining the relative positions. But that issue of the duplicated values will always pop-up. Also will the search method account for when there are corresponding duplicates in both columns A and F. Attached is the true spreadsheet I am playing with may be this will give you guys an idea what the data looks like.

Thanks again.

I have a hard time explaining but this is the best explanation I can come up with.

Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positions

LinkBack

Thread Tools

Rate This Thread

Display

Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positions

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Re: Macro - Search/Match Values in a Coloumn and Return values, & maintain relative positi

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return Multiple values from a column with index and match, and search criteria

Is there a formula that will search for nonspecific values and return a match?

[SOLVED] Lookup multiple column values that match search criteria and return row labels in one cel

[SOLVED] How to return multiple values that match a single search criteria?

Return ALL relative positions of a value within an array

Bookmarks

Bookmarks

Posting Permissions