Free Microsoft Excel 2013 Quick Reference

Need to Compare three columns for matching values and populate a predetermined Value.

I have three columns A, B and C and need to compare the text values in each cell in column A exists/matches to any of the cells in column B and column C. If it matches to column B and C I need to populate a value 2 in a new column D else I need to populate a value 1 in column D if it matches to only one of the column and a value 0 if it does not have a match in either of the columns. I tried using a vlook up and a match condition but to no avail. Any help on this regard would be highly appreciated.


Post your answer or comment

comments powered by Disqus

Related Results

  1. Formulas: Need to compare 3 columns of data with sp
  2. VB Macro to compare two columns from different worksheets and return a 3rd column of values
  3. Using functions to compare multiple columns for mismatch of cells
  4. Comparing 2 column for matches
  5. Using functions to compare multiple columns for mismatch of cells
  6. Compare two columns of two tables and extract the the matching number's raws into ano
  7. Matching two values to two other values and returning a 3rd value
  8. Using a function to compare three columns
  9. Compare Two Columns & Copy Matching Rows To New Sheet
  10. I need to compare to columns and indicate the matches in another
  11. I need to compare to columns and indicate the matches in another
  12. Search and Compare two Columns for differences (New Entries) Macro
  13. Compare 2 columns of text - find partial matches
  14. Compare Columns from different Workbook and Paste the value not present in the first
  15. Search Multiple columns for criterion asterisk (*) and Return Numeric Label
  16. Search Multiple columns for criterion asterisk (*) and Return Numeric Label
  17. Find date within a range of dates and input a value in the next free cell
  18. Manipulate formula if the cell value is #N/A or #VALUE!
  19. Compare multiple columns on 2 sheets for entire row match
  20. How to compare data in table and return sum of matching pairs?
  21. Check three columns for match to a single column
  22. Need to compare match the data of each column and then subtract it
  23. Match similar rows from two sheets using three columns to match the "paired" rows
  24. Comparing Two Columns for Multiple Results
I need to compare three columns of data.

Column A = Executive Name
Column K = Resource Type (Employee, Contractor, Vacant, etc)
Column X = State (AL, CA, FL, etc)

I need a count of the number of vacancies working for Executive Smith that are in a set of States.

What I've tried repeatedly is an Array. I know you discourage this but I'm under a deadline....surprise ;-)

My formula(s) have looked something like this:

{SUM=(('Sheet Name'!$A$2:$A$2000="Smith")*('Sheet Name'!$K$2:$K$2000="Vacant")*(............

I've tried OR functions at the end, no luck.
I've tried creating a new column on another sheet that performed an IF
statement that created a new Column that represented the set of States I
need, but still no luck.

I've attached a very small sample of the file I'm using. Should give you an idea of the formatting and data.

Thank you,

Aaron

I have a feeling this is very simple, and I still can't figure it out

Hey all

I want to create a macro that will allow me to compare two columns of data from 2 different worksheets and then if it finds a match creates a new column in the 2nd worksheet containing the value from the 2nd column in the first worksheet which corresponds to the first matching column. For Example

Worksheet 1 Worksheet 2

Column A Column B Column A New Column
On 1 Off 2
Off 2 Reset 3
Reset 3 On 1

So I kinda tried to create a graphical representation of what I want it to look like. Basically I want to compare Column A of worksheet1 with Column B of worksheet 2, with Column A from worksheet 2 not necessarily in the same order as Column A in worksheet 1. Column B in worksheet 1 is the corresponding numerical value for the text in Column A. I would like the macro to compare both Column As and if there is a match return the corresponding value in Column B to a New Column in worksheet 2. I'm kinda new at VB, so any help with be great.

Hmmm... having only 10 words as a subject to describe this problem seems
rather limiting. Come on, Microsoft, let me have 15 words!

I want to use Excel functions to compare multiple columns for mismatch of
cell contents. I spoent a lot of time using Excel Help for functions like
VLOOKUP and IF, with no luck. I assume there is a functional solution, but I
could not figure it out in an hour. These are not easy functions for me to
use.

The TASK: compare name (text) AND a number in a set of columns with similar
(but not exact) info in another set of columns. I want to be able to have a
text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number
mismatch".
There are more entries in one set of columns than another.

After much research, and trial & error, I was unsuccessful using a single
function to get the desired results.

I tried consolidating the text & number information in an additional column
using this formula: =B403&" "&F403 - info was in columns B & F

Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the
consolidated info. The result was showing as, "#N/A", but it did not
distinguish between a mismatch of cell contents, or if matching information
was missing from a column.

Can you help me with this?

Mark D.
Boston, MA

I am wanting to compare 2 columns for exact matching. If there are two
matching items I want to be able to say "A match" in a chosen cell for all
the ones that match. Column C will be retrived in an random order... so how
would I write the formula for that?????

Example:
A B C
45time 11tune
A match 11tune 89time
47doog 43jkjkj
A match 123ABC 123ABC

If possible include how to highlight the ones that make as another option.
Thanks

Hmmm... having only 10 words as a subject to describe this problem seems
rather limiting. Come on, Microsoft, let me have 15 words!

I want to use Excel functions to compare multiple columns for mismatch of
cell contents. I spoent a lot of time using Excel Help for functions like
VLOOKUP and IF, with no luck. I assume there is a functional solution, but I
could not figure it out in an hour. These are not easy functions for me to
use.

The TASK: compare name (text) AND a number in a set of columns with similar
(but not exact) info in another set of columns. I want to be able to have a
text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number
mismatch".
There are more entries in one set of columns than another.

After much research, and trial & error, I was unsuccessful using a single
function to get the desired results.

I tried consolidating the text & number information in an additional column
using this formula: =B403&" "&F403 - info was in columns B & F

Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the
consolidated info. The result was showing as, "#N/A", but it did not
distinguish between a mismatch of cell contents, or if matching information
was missing from a column.

Can you help me with this?

Mark D.
Boston, MA

i need a macro code to prepare bank reconciliation
i need to compare two columns of two tables and extract the the matching number's raws into another sheet (column has empty cell can fill up with nil text and can sort column a to z)
example excel sheets are added
i want customer ref column (column B) of bank statement compared with column F of bank ledger then extract the matching number raws from both sheet into another sheet leaving a column of space between them
then do same with credit column of bank statement (column e) and debit column of bank ledger (column G)
lastly compare and extract debit column of bank statement (column f) and credit column of bank ledger (column H)
both bank statement excel workbook and bank ledger excel work book added
please give me code it is year end and i have tedious task of preparing lot of bank reconciliation in my shoulder ,if i get this code it will save lot of time as the last amonts leave in sheet after extraction will be discrepancies
only extract numbers
please help me download excel files
thanks in advance

Hi guys,

I've searched the FAQ but can't find what I'm after!

I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.

What I want to end up with, is which postcode the incidents are in!

So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.

Here's what it looks like!

Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581

Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444

I want to end up with

Incident Postcode
01 SP7185H

etc etc.

Can anyone please help ASAP??

Many thanks

Chix

Hi everyone! I'm experiencing brain freeze & need some help...I need to compare information in three columns to find the highest value & return the name of the person with the highest value. I want to use the IF function to make the comparisons for me, but I can't get my function to work properly. Basically, my data is as follows:

b c d
12 3 7
5 9 8
12 10 15

but my function is =IF(b3>c3,b3>d3,"Alice","Bob"), which ignores the possibility that column d could be the largest & need to return John's name.

I can't figure out the correct syntax to nest another IF function so that if col d is larger than columns b & c, John's name will be returned. Would someone be kind & guide me in the right direction?

Thanks very much for your help!

Brainfreeze

Good afternoon,

I have searched the forums for answers and guides but I need more assistance.

I need to track only certain tasks id's out of entire task id database.

I have 1 column of data on sheet "A" that represents task id's I want to track. On sheet "B" I have multiple columns of data, the first column being the task id's. I need to compare the column on sheet A with the first column on sheet B. If there is a matching task id, I need to copy the entire row of data from sheet "B" on to a new sheet "C". If there is no matching task id on sheet "B", I would like to copy just the task id.

Each week I would get a new set of data for sheet "B" (which can vary in length) and I need the ability add or subtract task id's on sheet "A" that need tracking.

Please see the attached xls file for a piece of sample data.

Any help or direct is appreciated. Thank you in advance.

am wanting to compare 2 columns for exact matching. If there are two
matching items I want to be able to say "A match" in a chosen cell for all
the ones that match. Column C will be retrived in an random order... so how
would I write the formula for that?????

Example:
A B C
45time 11tune
A match 11tune 89time
47doog 43jkjkj
A match 123ABC 123ABC

If possible include how to highlight the ones that make as another option.
Thanks

am wanting to compare 2 columns for exact matching. If there are two
matching items I want to be able to say "A match" in a chosen cell for all
the ones that match. Column C will be retrived in an random order... so how
would I write the formula for that?????

Example:
A B C
45time 11tune
A match 11tune 89time
47doog 43jkjkj
A match 123ABC 123ABC

If possible include how to highlight the ones that make as another option.
Thanks

Hello All,

I'm trying to compare two columns for new entries. Column A is the source information and column B is the coulmn to compare against. If there are new entries in column A I'd like the macro to record these new entries. Just the values would be perfect. For example if I have two columns with;
____A____B
1__105__105
2__106__106
3__107__107
4__108__108
5__109__109
6__111__111
7__112__112
8__113__113
9__114__114
10_115__115
11_116__116
12_117__117
13_118__118
14_119__119
15_120
16_121

I would like my macro to tell me that 120 and 121 are not in the second column. My columns are much longer and can have entries that are not directly across from one another (example; A3 and B3 may be 103 and 676 respectively). I'm guessing I'll need a search that uses column A as its search numbers and that it will search through column B and keep track of what numbers are not found. I can see all the suedo-code in my head but I'm not good enough yet on VBA to pull this one off. Any ideas are good ideas.

Thanks everyone in advance.

Matthew Acker

I need to compare 2 columns of hospital names and find "duplicates." (Column A is all the hospitals we've already reached out to, and Column B is a new source list of hospitals, some of which we might have already reached out to and thus overlap with A.) But since the data was drawn from various sources, different names might refer to the same hospital (e.g. ABC Hospital - Main Campus, ABC Hosp - Main, ABC-Main, The ABC Hospital). I tried to set up a conditional formatting to highlight duplicates and facilitate their deletion from Column B, but for that to work it seems cells are only recognized as duplicates if the text is exactly identical. I had the idea of doing the comparison based on keywords in each cell (e.g. to recognize just "ABC" and ignore words like "hospital") but am not sure if this is even possible or the best way to approach this.

Any suggestions for this excel noob?

Hope the question made sense... thanks for your help!

Hi All,
I need to compare two columns from different workbook, and copy the name that is present in the first workbook but not present in the second workbook and paste it at the end of the column of the second workbook.

I need to do all this using Macro.

I am also attaching the dummy files. I need to update the Names, Grade from the first file (copyfrom.xlsx) to the second file(copyto.xlsx).

Thank you all for your help.

Hi All,

I would like to search multiple columns for criterion asterisk (*) and Return
across a single Row Multiple Numeric Labels that MATCH the criterion on their
respective Row.

1. The criterion is an asterisk * (multiplication sign) housed in cell A2
2. The data to be searched for criterion is housed in columns U2:Y60
3. The Numeric Labels to be returned are housed in column T2:T60
4. To search for criterion "~**" (Tilde ** in quotation marks)

Sample Data Layout:
Col"T" Col"U" Col"V" Col"W" Col"X"
Col"Y"
1
2
3 X
4 **
5
6 **
7 * **
8 X
9 **
10 *
11 * *
12 *
13
14 **
15 X

Expected Results:
Numeric Label has criterion asterisk on its row. Return Numeric Labels across
a row :
4 6 7 9 10 11 12 14

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi All,

I would like to search multiple columns for criterion asterisk (*) and Return
across a single Row Multiple Numeric Labels that MATCH the criterion on their
respective Row.

1. The criterion is an asterisk * (multiplication sign) housed in cell A2
2. The data to be searched for criterion is housed in columns U2:Y60
3. The Numeric Labels to be returned are housed in column T2:T60
4. To search for criterion "~**" (Tilde ** in quotation marks)

Sample Data Layout:
Col"T" Col"U" Col"V" Col"W" Col"X"
Col"Y"
1
2
3 X
4 **
5
6 **
7 * **
8 X
9 **
10 *
11 * *
12 *
13
14 **
15 X

Expected Results:
Numeric Label has criterion asterisk on its row. Return Numeric Labels across
a row :
4 6 7 9 10 11 12 14

Thanks
Sam

--
Message posted via http://www.officekb.com

I have a list of dates in sheet 1 and a second sheet with a range of dates. I need to compare the first list of dates and have the "period" value input when the date falls in the range. I used the sumproduct and index/match but get a "0" not the value in the relevant cell.

I have attached a file which has the data and the formula I used which did not work.

Hi All,
I am new here. I am using Excel 2007. I have this excel calculation to look up max value and gives a string value such as "Available", "Maybe" or "Not Available".
I put this conditions to cell G8:G10.

My problem is sometimes the cell value of H8:H10 gives the value of #N/A, but I want to change the #N/A to "NO VALUE"
Here is the formula in cell S12 where i put this formula to lookup if there is a value.
=INDEX(G8:G10,MATCH(MAX(H8:H10),H8:H10,0)).

What I want is if the value of H8:H10 is #N/A, excel didn't have to find match in G8:G10, just gives the value of "NO VALUE"

Is there anyone can give me any idea how to manipulate the formula?

Thank you in advance.

Hi there, I am needing to compare 2 columns from Sheet1 with 2 columns on Sheet2 and display a message when both columns match. I have this working somewhat, but my problem is that I have multiple instances of the data that's in the columns. So, if column A matches anything on Sheet2 and column H matches anything I get a match. I need to only show a match when the two columns match "together". I need this in a macro that automatically runs upon change event instead of formula. It will be used in a template by several beginner level Excel users.

I'm currently using named ranges "manu" and "subp" from Sheet2 for the match.

Here's my sample code for the change event:


	VB:
	
 Range) 
    Set rTarget = Target 
    Dim totalrows As Long 
    totalrows = ActiveSheet.UsedRange.Rows.Count 
    hrow = "$H$2:$H$" & totalrows 
     
     
    If Not Intersect(Target, Range(hrow)) Is Nothing Then 
        On Error Resume Next 
         
        Application.EnableEvents = False 
        Run "Exemptions" 
         'Turn events back on
        Application.EnableEvents = True 
         'Allow run time errors again
        On Error Goto 0 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here's the code for the macro itself:


	VB:
	
 Range 
 
Sub Exemptions() 
    Dim c As Range 
     
    For Each c In rTarget 
         
        If Not Range("manu").Find(c.Offset(0, -7).Value, LookAt:=xlWhole) Is Nothing Then 
            If Not Range("subp").Find(c.Value, LookAt:=xlWhole) Is Nothing Then 
                 
                MsgBox "Check for Possible Lead Exemption" 
                 
                 
                 
            End If 
        End If 
    Next c 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.

Iíve got a table with three columns: the first column (column A) shows bus service numbers, the second (column B) indicates the route direction (circular, inward, outward) and the third column (column C) the respective mileage. I would like to add the mileage for matching inward and outward services so I know how long the return journey would be (the routes can be slightly different due to one-way streets for example). Here is a short example (my table contains a lot more services):

number direction mileage
3A Circular 3.18
5E Inward 9.47
5E Outward 9.43
5EB Outward 9.21
6A Circular 5.57

I think in a first step Iíd need to exclude all circular services by sorting the data, which is no problem. Then Iíd have to check if there is a matching pair in column A. Then, if there is such a pair Iíd need to check if one of them is an inward and the other one an outward journey in column B. If this is the case too, Iíd like to add up their individual mileages.

Any help on all steps would be greatly appreciated.

For the first step I tried MATCH but because the cellís value is within the range I am comparing it to, the result is always true (e.g. MATCH(A3,A$1:A$5,0)) and of course MATCH only returns the position of the matched value but I suppose I could overcome the latter by using an IF function and turning any number into TRUE?

For the other steps I donít have any ideas at all.

Maybe I am trying to achieve something that isnít possible but I donít hope so!

Many thanks for your help!
Chris

I have three columns, each with different pieces of information. I need to check whether any of these three columns has a match in a different column. For example, in columns A B and C I have "Red1" "Red2" and "Red3". I need to return TRUE if either "Red1" "Red2" or "Red3" are listed anywhere in Column E.

Thanks in advance.

i need some help on my excel sheet. i have two data generated from different source. i need to compare match the data of each column and then subtract it. the data that i need to match is the account number the date and the amount. if those data are matching then i will subtract the amount. for every matching data that i will have the total will return to zero.thanks!

Hi,

I have the final, and most important, step left in a matching macro to go but i think its over my head.

Effectively, after much sorting, i have data in two sheets of a workbook, one contains a large amount data('large sheet"), the other much less("small sheet"). For every row in the small sheet there is a similar, but not exact, row in the "large sheet". I would like advice on the code, i assume loop, to "match" the rows from "small sheet" to the large sheet using the three columns as identifying values. All unmatched trades are not needed, i would prefer to achieve this by copying the "matched" rows from the "large sheet" and pasting it into new sheet so that i can tidy it up, but at this stage(crash course on vba code wise), i should be able to tidy it myself(google). all relevant cells are numeric.

Extra:
Is this possible? i could if required, move the "small sheets" data around so that the matching data is contained the same columns in both sheets, but i assume that isnt necessary. also, i need to use three columns because the sheet is large and will be used so often that i want to to be robust enough to handle the likely event of unwanted matches, is that possible, extensive google searches have left me worried it's not.
The columns from the small sheet are E, F, I to match with the columns in G, I, L respectively.
Any help, regardless of how incomplete would be greatly appreciated, i've recently learnt a lot(relatively speaking) on VBA so i can hopefully build on any help.

Hi,

Appreciate it if anyone can assist me with this.

I have a table with two columns of data (should be identical) that i need to compare for different results i.e.

Table Sample

COLUMN A

KIK-TMM-30-EL-RP-1000-1
KIK-TMM-30-HU-PR-0901-C
KIK-TMM-30-HU-PR-0902-B
KIK-TMM-30-HU-PR-0903-A
KIK-TMM-30-HU-PR-0904-A
KIK-TMM-30-HU-PR-0905-B
KIK-TMM-30-HU-PR-0906-0
KIK-TMM-30-HU-PR-0907-C

COLUMN B
KIK-TMM-30-EL-RP-1000-1
KIK-TMM-30-HU-PR-0901-C
KIK-TMM-30-HU-PR-0902-B
KIK-TMM-30-HU-PR-0903-0
KIK-TMM-30-HU-PR-0901-C
KIK-TMM-30-HU-PR-0904-A
KIK-TMM-30-HU-PR-0905-2
KIK-TMM-30-HU-PR-0906-0

The required result should be:

1. Compare Column A (excl. last digit) to Entire Range Column B (excl. last digit) (i.e. A3 = KIK-TMM-30-EL-RP-1000 compare against B3:B8 and return value TRUE if match)

2. Compare Column A to Entire Range Column B (i.e. A3 compared with B3:B8 and return value TRUE if match)

3. Compare Column A to Entire Range Column B (i.e. A3 compared with B3:B8 and return value cell location of A2 value in range B)

Thank you for your help
V_MAX


No luck finding an answer? You could always try Google.