+ Reply to Thread
Results 1 to 11 of 11

Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

  1. #1
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    Hi all,
    Please solve my problem in the given excel file
    1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
    2. Sheet2; how to remove "B000" or "B00" from the list
    3. Sheet3; how to highlight difference or find out the difference between to lists

    Thanks in advance..
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    Sheet1 to add BOO
    =IF(LEN(A2)=4,"B000","B00")&A2

    Sheet2
    =--RIGHT(A2,5)

    Sheet3
    =A2=RIGHT(B2,5)*1
    or
    Conditional formatting (Select Column B)
    =A1<>RIGHT(B1,5)*1
    Appreciate the help? CLICK *

  3. #3
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    Thanks lot mate for your great help

  4. #4
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    you are welcome. Thank you for feedback

  5. #5
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    AZ-XL, In sheet3 I have most of numbers in both lists but not in the same row.. I want to highlight those which are not exist in the other list... and in sheet2 why we put two "--" in the formula..??
    Last edited by alipezu; 03-29-2014 at 10:40 AM.

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    In sheet3 to know whether list A matches or not
    =IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"No Match","Match")
    for list B
    =IF(ISERROR(MATCH(B2,Sheet2!A:A,0)),"No Match","Match")

    In sheet2 I put "--" because when Right function extracts last 5 letter its format being in text format. I put double minus to change their format to number. You can change "--" to others such as *1, +0, /1.

    To highlight numbers you should use Conditional formatting. If you use excel 2007 then you cannot make reference to other sheets. For that you have to use name manager and then use this name in conditional formatting.

  7. #7
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    for what purpose can I use "*1, +0, /1" these functions???
    And can I match two different columns in two different workbooks???
    Thanks

  8. #8
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    Quote Originally Posted by alipezu View Post
    for what purpose can I use "*1, +0, /1" these functions???
    When we use text functions (ex right, left, mid, etc) to extract numbers their format will be as text. For example. "000556" is in text format. To change format of it we should do mathematical operations. Double "--" changes value firstly to -556 then second minus makes it 556. As well as others. Listed operations do not change value of numbers but they change format.

    It is possible to match in two different workbooks but these workbooks have to be open whenever you work on your file.

  9. #9
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    Thanks alot.. can you please give me an example how to look for match in two difference workbooks.. eg: I have workbook1 and workbook2

  10. #10
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    601

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    For example. If you want match from workbook2 you can use this function
    MATCH(C2,'Workbook2'!$I:$I,0)

  11. #11
    Forum Contributor
    Join Date
    02-12-2014
    Location
    al ain
    MS-Off Ver
    Excel 2007
    Posts
    143

    Re: Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

    thank you !!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Add or remove info to lists and highlight differences between two lists
    By alipezu in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-30-2014, 08:51 PM
  2. Show Difference of sheet1-sheet2 in sheet3 if items match?
    By nyichiban in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2013, 04:03 PM
  3. Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  4. Replies: 2
    Last Post: 07-01-2013, 08:30 AM
  5. Replies: 5
    Last Post: 05-21-2010, 04:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1