Free Microsoft Excel 2013 Quick Reference

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.

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

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

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.

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

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

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 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

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

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

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.

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

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

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

Any suggestions for this excel noob?

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

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.

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 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 attached a file which has the data and the formula I used which did not work.

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.

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

Here's my sample code for the change event:

VB:Here's the code for the macro itself: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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Any help would be greatly appreciated.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

Thanks in advance.

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.

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