+ Reply to Thread
Results 1 to 4 of 4

Compare two ranges of data and give unique values in two different columns ignoring blanks

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2010
    Posts
    30

    Compare two ranges of data and give unique values in two different columns ignoring blanks

    Hi all ,

    I have huge feeds of algorithm test cases for a tool .. My job is to identify the blocks where there is difference in the algorithm output and the input feed ..

    -----------------------------------------------------------------------------------




    Column A contains the feed data and few blank cells

    example

    BOL CWB 8690-6086(SIZE/COLOR)(
    PAEDABNMNBLLOAJP-2948
    PAEDIBEKNILLOAJP-2952
    PAEDABKONDLLOAJP-2949
    PAEDIBNMNGLLOAJP-2950
    PAEDABIGGOLKOAJP-2947

    )


    BOL CWB 8647-1000(SIZE/COLOR)(
    PAEDIBAKPJLJOAJP-2944
    PAEDABFNPLLJOAJP-2945
    PAEDIBFDCBLIOAJP-2942
    PAEDABNAPGLJOAJP-2943
    PAEDABNAPGLJOAJP-Extra feed


    )

    Variaton theme : size

    --------------------------------------------------------------------------------------

    Column B contains the Algorithm data


    PAEDABKONDLLOAJP-2949
    PAEDIBNMNGLLOAJP-2950

    PAEDABIGGOLKOAJP-2947
    PAEDIBAKPJLJOAJP-2944
    PAEDABFNPLLJOAJP-extra algorithm data

    PAEDABNMNBLLOAJP-2948
    PAEDIBEKNILLOAJP-2952

    Detected Theme : Size


    -----------------------------------------------------------------------------------------



    I want a macro to do the following .

    Compare the entries in column A with entries in column B . Ignore blank cells and Cells with "(" , ")" and "theme"


    Output in column E will be count of entries in A
    output in column F will be count of entries in B
    Output in column G will be the values in A which are not in B
    Output in column H will be values in B which are not in A

    ------------------------------------------------------------------------------------

    Expected outputs for the example are ..

    Column E

    10

    Column F

    7

    Column G

    PAEDABNAPGLJOAJP-Extra feed
    PAEDABFNPLLJOAJP-2945
    PAEDIBFDCBLIOAJP-2942


    Column H

    PAEDABFNPLLJOAJP-extra algorithm data


    Sample data attached with desired output example

    Autovar.xlsx




    --------------------------------------------------------------------------------


    It will be helpful if someone can write the code to work on ranges rather than the whole column . Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    21,868

    Re: Compare two ranges of data and give unique values in two different columns ignoring bl

    See if this is how you wanted
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Compare two ranges of data and give unique values in two different columns ignoring bl

    @jindon thanks a lot .. works perfectly .. i did some modifications of code for instead of counting the data with "-" ... changed it to exclude blanks and parenthesis and the words with "theme" in it .. Il be really thankful if you can fill the cells of values in column A with light red fill ( ones we are outputting in column G ) and cells of values in column B with yellow fill (values which are outputting in column H) and also extend the border lines for the respective tables .. can you also populate a column I with the Table number ?? .. say the first block has table number of 1 .. when the new block starts the table number will be 2 for the block and so on ...

    P.s : I owe you my job :D ..
    Last edited by rampulaparthi; 09-16-2013 at 07:41 AM.

  4. #4
    Registered User
    Join Date
    06-20-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Compare two ranges of data and give unique values in two different columns ignoring bl

    I solved the borders part too .. Ive pretty much changed what i needed .. thanks a lot ... you are real savior .. Arigato gosaimus ..

+ 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. Replies: 8
    Last Post: 09-12-2013, 09:17 AM
  2. Compare 2 columns of data and get unique values in the 3rd column
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 04-04-2013, 08:32 AM
  3. Replies: 1
    Last Post: 02-18-2013, 01:46 AM
  4. Macro to sum values in one column based on data in another colum while ignoring blanks
    By The Machinist in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-15-2012, 03:25 PM
  5. Replies: 5
    Last Post: 12-02-2010, 01:10 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