Free Microsoft Excel 2013 Quick Reference

compare Sheet1 against Sheet2 with highlighting results in Sheet1

hello everyone
...Now, i am stuck with how can i do this another tedious and hard excel problem i dealt with. How can i compare a master list in Sheet1 against actual values in Sheet 2 and with a result of highlighting the values that are in Sheet 2 in "Sheet 1".I hope i am clear...Sorry for my poor English. I will try this way; whatever values i have in sheet 2 that are found in Sheet 1-it should be highlighted (in any color) in Sheet1. I have attach a sample file. Please help me when you can. I would be very very thankful to you.

Im new to this site, I am actually learning how to post and be registered...and this is the best way so far that i think i can do to ask help from you, the experts.

best regards,
mariposa


Post your answer or comment

comments powered by Disqus
Scenario (using Excel 2003):
I have two worksheets.
"Sheet1" has 500 entries with the columns Email Address, First Name, Last Name
"Sheet2" has 3000 entries with the columns First Name, Last Name, Buddy Name, IM Platform

Problem:
I need to compare Sheet1 against Sheet2. For every person in Sheet1 that has a matching First Name and Last Name found in Sheet2 copy all instances of their Buddy Name and corresponding IM Platform into new columns. The trick here is that a person may have multiple buddy names for multiple IM Platforms (i.e. AOL, MSN, Yahoo, etc).

Sample Data:
Sheet1 -
rob.smith@google.com | Rob | Smith
rick.tyler@xyz.com | Rick | Tyler

Sheet2 -
Rob | Smith | HotDog23 | MSN
Rob | Smith | Hot_Dog@aol.com | AOL
Rob | Smith | yHot | Yahoo
Rick | Tyler | keyz5 | MSN

etc...

I need to grab all those entries for each user found in Sheet2 and their Buddy Names and IM Platform

I'm not an Excel programmer so I'm at a loss on how to start this. Any and all help would be greatly appreciated.

Good day to everyone!

I believe, there are smart and kind people out there who share their expertise in excel programming or the likes. I have a very manual "litic" job everyday that eats my big time at work. Pls help me hasten and improve my work and performance.

I find it hard to compare Sheet 2 against Sheet 3, in such a way that what ever input i have in Sheet 3 (only 1 column) will result to being HIGHLIGHTED ( in any color) in Sheet2. But provided that only 8 characters be pasted in Sheet 3.I mean,No matter how long is the partnumber or partname i copied from other workbook or worksheet, if i paste it in Sheet 3 (on the 1 column) "only the first 8 character must be displayed" And this 8 characters that were in Sheet3, if it can be found in Sheet2 from any cell, the cell would be highlighted.

Then, it would be nice if i have button in Sheet1 to click like named as "compare", in such a way that when i click the "compare"button, the comparison will took place.

I am so sorry with my English. I try my hardest to make you understand. Hope you could help me solve my problem.

thank you so much. Have a nice day!

Mariposa

btw, i attach sample file. Pls. see.

I have two workbooks (2005 Sales, 2004 Sales), which track daily results in
half hour intervals. I want to be able to show the increase in 2005 in a 3rd
workbooks. The first two workbooks are identically formatted. How can I do
this? Many thanks to all in the forum who have helped in the past.

Hi, I am searching for the macro code which should compare two excel sheets (Sheet1 and sheet2) and highlight the unmatched data in Sheet2. Here, cell to cell comparision is not suitable because the data in the other sheet might exist but not in the same cell. Hence using vaule of one cell of sheet1, we have to search the sheet2 (in all the cells) and then if it doesnot exist, highlight that particular cell of sheet1 with some color.
Please help me. I am attaching the sheets to be compared and the sample data.
Sheet1 data will be available in sheet2 but sheet2 will have more data.

Thanks..

sheet2.xlssheet1.xls

Hello Everyone,

I am new to this forum. Though I am comfortable using excel but not good at VBA. At present I need help to compare two excel sheets and want result in third excel sheets.

I have tried help given in different thread related to compare excel but could not get desired result.

Here is explanation of my problem:

Sheet1 contains Amount in thousands against each sl.no. and major head for plan and non plan for various financial year.

Sheet2 Contains amount in (thousand/10000) sl. no. and major head for plan and non plan for various financial years

In third sheet I need to find differences in all sl. no. and major heads for plan and non plan as given in sheet3

Also sheet3 should contain union of sl.no. and major head. If there is no data for these in sheet1 and sheet2 it should contain 0 as value for amount.

Sheet3 is the sample of format I need to find differences.

Please help me.

Vicky

Hi

I have data in
Sheet1 G2:G500
Sheet2 G2:G500

I need something to find where cell in Sheet1 collum G matches in Sheet2 collum G and then get difference from Sheet1 collum D and Sheet2 collum D in the rows where it found

example

42K123456 is in G2 sheet1 and then in G40 sheet2
now D2 from sheet 1 minus D40 from sheet2, result in sheet1 I2

this is a real difficult one

Hi Friends,

I have written a macro for comparing sheet1 with sheet2, it is comparing sheet1's row with sheet2's row and if the row is not matching with sheet row then the the entirerow from sheet2 will be moved to third sheet3.

my requirement is a row from sheet1 needs compare all the rows in the sheet3 and unmatched rows needs to be copied to sheet3

Please kindly modify my attached macro or provide the logic.

Thanks & Regards
Ram

I have a table that the user enters a number for X and a number for Y and gets the corresponding results outputted from the tabled range. How can i highlight the results in the table?
Note: the results are not unique.

The formula i am using to get the results is this: =INDEX(mTable,MATCH(ROUND(Y7,0),xinc,0),MATCH(ROUND(AA7,0),bearing,0))
which rounds the x = xinc and y = bearing and looks it up in the mTable.

If possible too, if there is a way to highlight the results, is it possible to highlight all the numbers for the X and Y to the results?
Example: if the user enters x = 30 and y = 45 and the result is 10. Is there a way to make the 10 highlight in Green and every number from the left x to the results highlight in yellow. The same with the y. every number from the top y to the results highlighted in yellow as well. Sort of painting a line to the results.

These charts get rather large and at times even seeing the results confuse the user as to what the start was.

Thank you for any help

Hi the experts,

I need your help with a VB code that can remove the rows to copy onto Sheet2 with any cells background filled in RED color in column A Sheet1.

If would be even better if the code can also copy the headers name in row 1 to paste onto row 1 in Sheet2.

Thanks in advance for your help.

Hi,
I'm very new to Excel formuals & VB macros.
Could you please help me with the following code.

I've 2 excel sheets and I need to paste data from Sheet1 to Sheet2 with conditions on Sheet1 values.

Let's say I've Sheet1 as follows.
===A===============B==========C============D============E============F=======(Excel columns)
1|Q1 04 Qty=======Q2 04 Qty=======Q1 04 Prc=======Q2 04 Prc=======Q3 04 Qty=======Q3 04 Prc=== (Texts)
2|==111==============11==========222==========22===========333=========33===== (values)

I'd like get the following information onto Sheet2 as follows.

===A=====B=====C=====D===
1|Data === Q1 04 === Q2 04 === Q3 04
2|Qty === 111 === 222 === 333
3|Prc === 11 === 22 === 33

If it is not clear, for your convenience, Enclsoed please find 2 attachments.
Initially, data is like in attachment Sheet1.jpg and I'd like to get the data from sheet1 into Sheet2 like in attachment Sheet2.jpg

where Q1 04 means Quarter1 of 2004 & Q2 04 means Quarter2 of 2004
Qty means Quantity
Prc means Price

In Sheet1, quarters are DYNAMIC. current example shows for 3 quarters. But, It could be 1, 2 or 3 quarters.
and quarters are not in order. (THIS IS VERY IMPORTRANT TO BE NOTICED) current example shows Qty & Prc for Q1 & Q2 1st and then shows Qty & Prc for Q3.
If it is 1 quarter, Sheet2 will be just 2 columns & 3 rows as follows.

===A=====B==
1|Data Q1 04
2|Qty 111
3|Prc 11

I'm not sure if this can be achieved using excel formulas. If not, I appreciate if you can help me with necessary macro coding.

Hello
How to replace a function with its result in a formula? For example,
=INDEX(...)+INDEX(...) with
=result_of_INDEX(a...)+result_of_INDEX(b...)=1025+ 1307.
How to replace a function with its resulting reference in a formula? For
example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11.

My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The Header
rows;
In Sheet1 - Column 19 is label "STATUS".
How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
(with the Same headers) ONLY
for records (on Sheet1) where STATUS = "RELEASED" (without the quote marks)
?
Tks in Advance,,
Jim

Hello
How to replace a function with its result in a formula? For example,
=INDEX(...)+INDEX(...) with
=result_of_INDEX(a...)+result_of_INDEX(b...)=1025+1307.
How to replace a function with its resulting reference in a formula? For
example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11.

Hi,

Can anybody help me with my problem? I have Excel worksheet with two sheets (sheet1 and sheet2) and three columns in each sheet (column A is a called Number, column B is Price and clumn C is Name).

In sheet1 and sheet2 I have rows from 2 to 50 in each column but only in sheet1 I have compelete list of information (Number e.g 1234567, Price e.g 32 and Name e.g tool). In sheet2 I have only Number column filled.

Now I need macro wich is checking out sheet1 Number column and if it found similar value than in sheet2 Number column it copied Price and Name cells to after that Number (at the same row).

I hope someone figured out what I meant because English is not my strongest skill..

Thanks,

Sami

My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The Header
rows;
In Sheet1 - Column 19 is label "STATUS".
How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
(with the Same headers) ONLY
for records (on Sheet1) where STATUS = "RELEASED" (without the quote marks)
?
Tks in Advance,,
Jim

What formula do I need to traspose sets of data from Sheet1 to Sheet2?

the data comes in sets of 8, sheet1 looks like this (from L3)

L M

3 POCKET 1
4 DIE 1
5 CC-AUTO 12
6 CC-CALC 4
7 EB 3
8 CC-FLOW 6
9 EB-FLOW 1
10 Total Void 42
11 POCKET 2
12 DIE 1
13 CC-AUTO 23
14 CC-CALC 0
15 EB 2
16 CC-FLOW 12
17 EB-FLOW 2
18 Total Void 37

Sheet2 needs to look like this (from C7)

C D E F G H I J

7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total
Void
1 1 12 4 3 6 1 42
2 1 23 0 2 12 2 37

I want to look for a match the Headers on Sheet2
("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in
Sheet1, Column L3+ and get the value of Column M3+.

This is what I have working so far..

=OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1)

How do I make this formula skip every 8 rows?

Thanks,
CJ

Hi Gurus,

Would need your kind expertise to help on this request.

Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas to get the intended output as shown below.

Sample data (Sheet1):
ClassD | ClassB | ClassC | ClassA

Sample data (Sheet2):
Group1 | Group3 | Group2 | Group4 -> This is the header
ClassA | ClassA | ClassB | ClassD
ClassC | ClassC | ClassC | ClassC
ClassD | ............| ClassA

Sample output data (Sheet3):
ClassD | ClassB | ClassC | ClassA -> This is the header
Group1 | Group2 | Group1 | Group1
Group4.................Group2 | Group2
...........................Group3 | Group3
...........................Group4

Purpose: to find and match data from sheet1 against sheet2 and project output in sheet3.

For example:
Sheet1 -> ClassD
Sheet2 -> Check and find which group "ClassD" does it belong to.
Sheet3 -> Find, match and capture the output.

Note:
If the there is no matching, need to indicate with some message like: "no match".

Please refer to the attached file for some sample data.

Appeciate for any of your help and advice.

Thank you in advance.

- Jack

Dear Admin/Moderators/Seniors

I need help for salary calculation also how to get inforamtion from sheet1 to sheet2 if i enter in sheet1 (Attendance Register) it should automatically comes in sheet2 (salary Calcuation).

Also i want to select month in attendance register if i change the month the days and dates should comes automatically and if sunday it should hilighted in red colour.

Please find attached file and go through the formulas and calculate same like that.

VBA password is : Carrot

Pls help me.

Thanks in advance.

Regards,
Logu

Hi all

Im trying to figure out if it is possible to insert text from a cell from another sheet to a another cell with text already in the new cell.

Example

IPoverEthernet ethernetIpAddress= "xxx.xxx.xxx" ethernetSubnetMask="255.255.255.248"

Where xxx.xxx.xxx is, comes from another sheet within the workbook. At one time, many moons ago, I thought I could do this, but have forgotten since then.

Any assistance would be great.

Thanks

I am willing to pay $50.00 to anyone who can fix my code. It seems to be just something i am missing because it use to work and now does not. If you need further info by phone I can communicate that way as well .

I have an excel macro that compares 2 sheets called "Recall" and "Equip". I want to compare column C in Recall against Column A in Equip. For the items that match I want it put into a 3rd work book called "Results". In this "Results" workbook I want the corresponding info from the items that match from both workbooks in "Recall" and "Equip". I have developed most of the code but am stuck at the very last point.

Worksheet A: "Recall" is a list of equipment with corresponding info
Worksheet B: "Equip" is a list of equipment with corresponding info
Worksheet C: "Results" is the matches between A and B with corresponding info from both
Worksheet D: "Sheet1" is to have the info from A so I can copy that info back into C
Worksheet E: "Mismatch" is the equip on A that does not match with B

Therefore if there are 60 items on A and only 55 match with B there will be 55 items with corresponding info from A and B on worksheet C. There will be 5 items on Worksheet E.


	VB:
	
 
Private Sub CommandButton1_Click() 
     
     'Clears the cells
     
    Range("A2:AE65536").Select 
    Selection.ClearContents 
    Range("A1").Select 
     
    Call ClearSheet 
     
    Dim Sht1Rng As Range 
    Dim Sht2Rng As Range 
     
     'compares the ID cell in both worksheets to each other
    Set Sht1Rng = Worksheets("Recall").Range("C6", Worksheets("Recall").Range("C65536").End(xlUp)) 
    Set Sht2Rng = Worksheets("Equip. ").Range("A7", Worksheets("Equip. ").Range("A65536").End(xlUp)) 
     
    For Each C In Sht1Rng 
        Set d = Sht2Rng.Find(C.Value, LookIn:=xlValues) 
         'If same value found in col A of Data2 sheet then copy
        If Not d Is Nothing Then 
            C.Resize(, 28).Copy Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0) 
             
            Set d = Nothing 
        ElseIf d Is Nothing Then 
             
             
            C.Resize(, 28).Copy Worksheets("Mismatch").Range("A65536").End(xlUp).Offset(1, 0) 
             
            Set d = Nothing 
        End If 
    Next C 
     
     ' Centers all the data in the cells
    Sheets("Results").Select 
    Range("A2:AD900").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
         
    End With 
     
     
     'Sorts the data based on the Cal Date
    Cells.Select 
    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("M2:M900" _ 
    ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
     
    With ActiveWorkbook.Worksheets("Results").Sort 
        .SetRange Range("A1:AD900") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     'sets the format the data to general
    Cells.NumberFormat = "GENERAL" 
     
     
    Range("L2:M900").Select 
    Selection.NumberFormat = "m/d/yyyy" 
     
     
     
    ActiveWorkbook.Worksheets("Sheet1").Range("A1:AA900").Clear 
     
     
     'compares to other
    Dim Sht1 As Range 
    Dim Sht2 As Range 
     
     'compares the ID cell in both worksheets to each other
     
    Set Sht1 = Worksheets("Equip. Cal by ETL").Range("A9", Worksheets("Equip. ").Range("A65536").End(xlUp)) 
     
    Set Sht2 = Worksheets("ETL Recall").Range("C6", Worksheets("Recall").Range("C65536").End(xlUp)) 
     
    For Each d In Sht1 
        Set C = Sht2.Find(d.Value, LookIn:=xlValues) 
         'If same value found in col A of Data2 sheet then copy
        If Not d Is Nothing Then 
            d.Resize(, 28).Copy Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) 
             
            Set d = Nothing 
        ElseIf d Is Nothing Then 
             
             
            C.Resize(, 28).Copy Worksheets("Mismatch").Range("A65536").End(xlUp).Offset(1, 0) 
             
            Set d = Nothing 
        End If 
    Next d 
     
     
     ' Copy Code and Comments
    With Sheets("Sheet1") 
        .Range(.Range("F2"), .Range("F65536").End(xlUp)).Copy 
    End With 
    Sheets("Results").[AD65536].End(xlUp)(2).PasteSpecial Paste:=xlValues 
     
     ' Copy Code and Comments
    With Sheets("Sheet1") 
        .Range(.Range("G2"), .Range("G65536").End(xlUp)).Copy 
    End With 
    Sheets("Results").[AE65536].End(xlUp)(2).PasteSpecial Paste:=xlValues 
     ' Copy Cal Lab
    With Sheets("Sheet1") 
        .Range(.Range("C2"), .Range("C65536").End(xlUp)).Copy 
    End With 
    Sheets("Results").[AC65536].End(xlUp)(2).PasteSpecial Paste:=xlValues 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


COMPARE SHEET1 with SHEET2

Selected ranges in SHEET1 and SHEET2...

Rows = From 3 onwards upto last found data populated row

Columns = From A to Z

CHECK CELL BY CELL AND FILL COLOR RED FOR THE CELLS IN BOTH SHEETS OF MISMATCH DATA

hello,

good day! i have received a reply from registered user here, with my problem, and gave me the source code xycode.xls (pls. see attachment). when i tested it, it works fine.

but when i tried it in the real work,i copied another masterlist worksheet and paste it in Sheet2;then another worksheet and paste it in Sheet3; but when i run compare--- the program didnt work.
there is pop up that says: Run-time error'457' This key is already associated with an element of this selection."

i have attach another worksheet as an example to copy and paste to the excel program you make. Pls. try it, i have problem still with the application. I need your help xiaoy312.
PLEASE help me to solve my issue.

i tried contacting , sending message to the one who help me but i receive no reply . i dont know how to edit the source so that it will work fine. If you can help, i would really be thankful. I attach some examples, and her source:

Sub Compare()
Dim patternHL As New Collection
Dim LookUp() As String
Dim c As Range

'Added a highlight pattern, so you can define the color you want for each Key
ReDim LookUp(Feuil3.Cells(65535, 1).End(xlUp).Row - 1)
For i = 1 To Feuil3.Cells(65535, 1).End(xlUp).Row: Set c = Feuil3.Cells(i, 1)
patternHL.Add c.Interior.Color, CStr(c.value)
LookUp(i - 1) = c.value
Next

For Each c In Feuil2.UsedRange
If BeginWith(c, LookUp) Then _
c.Interior.Color = patternHL(Left(c.value, 8))
Next
End Sub

Function BeginWith(ref As Range, vArg As Variant) As Boolean
On Error Resume Next 'when length of ref is shorter than value's length, this crash
For i = LBound(vArg) To UBound(vArg)
BeginWith = BeginWith Or Left(ref, Len(vArg(i))) = vArg(i)
Next
End Function

thank you so much.

mariposa

Hi,

I have two worksheets holding similar information. The first worksheet MCIFILE_t holds lists of vendors with their addresses, whilst the second worksheet Vendors holds lists of Vendors by the type of business they do. I need to compare the vendors on the two worksheets and highlight any that match, ideally on the first worksheet.

The data range on the first sheet has been named Vat and on the second Vendors.

I saw from a similar previous post the following code to search by two criteria, but I'm having trouble adapting it to my needs;


	VB:
	
 aaa() 
     'dimension 2  variables as  worksheets, then set to the relevant sheets
    Dim SrcSH As Worksheet 
    Dim DataSH As Worksheet 
    Set SrcSH = Sheets("Sheet1") 
    Set DataSH = Sheets("PBIC 8") 
     'create an  array of the 2  headings to be checked on the source sheet
    headarr = Array("REGISTRATIONNBR", "SERIALNBR") 
     
     'cycle through the 2 heading items
    For i = LBound(headarr) To UBound(headarr) 
         'work out which column has the heading
        coll = WorksheetFunction.Match(headarr(i), SrcSH.Rows("1:1"), 0) 
         'make a  range of the relevant data in that column
        Set rng = SrcSH.Range(Cells(2, coll), Cells(Rows.Count, coll).End(xlUp)) 
         ' loop through the cells in the range
        For Each ce In rng 
             'if the cell has something in it then process
            If Not ce = "" Then 
                 'use  find to set a variable with the result
                Set findit = DataSH.Cells.Find(what:=ce.Value) 
                 'something is found
                If Not findit Is Nothing Then 
                     'make a note of the address for the first found entry
                    firstAdd = findit.Address 
                     'color the items found, then redo the find.  Keep going until you come back
                     'to the first address.  Covers  multiple appearances of an item.
                    Do 
                        findit.Interior.ColorIndex = 3 
                        ce.Interior.ColorIndex = 3 
                        Set findit = DataSH.Cells.Find(what:=ce.Value, after:=findit) 
                    Loop Until findit.Address = firstAdd 
                     
                End If 
            End If 
        Next ce 
    Next i 
End Sub 

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

Many thanks

hello freinds !!

i have 2 excel sheets and have to compare and search for a combination of cells then highlight color in sheet1

to be more specific
i have data in sheet1 were i need to look for the first 3 fields of sheets1 with the 3 fields of sheet2 and if found look for the value in cell of sheet2 with the column headers of sheet1
and color the cell which is immdetialy below the column header found

for example :
i have 3 fields like in sheet1


	VB:
	
country  area code name1 name2 name3 name4 
US         ny    1       sam   dirk    ste       hita 
us          va    2       jun     mic    atr        star 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and i have 3 fields in sheet2


	VB:
	
country area code origin 
us         ny   1       name2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
so it shuld walk thru each cell and check for the adjacent cell in sheet1 and highlight color for dirk

i have struct here for a while and couldnt able to solve the problem ..

any help would be appreciated


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