Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Formula to extract ref numbers

    I have reference numbers in sheet1 and reference numbers in sheet2

    I would like to compare the reference numbers on sheet1 with sheet input

    Where the reference number is on both sheets, I would like to extract the applicable reference number in Col D on sheet1. Sometimes the same reference number can appear more than once, but the values made differ

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    I don't understand your example.
    Can you have another go at explaining what you are trying to do.

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    My apologies . I will try to be clearer. I have reference numbers on sheet1 and on sheet Input. I want to compare the reference numbers on sheet "input" to "sheet1" for eg if KL1685 is on sheet "input" and on sheet1. I need a formula or a macro extract this in Col E on Sheet1. The problem is that the same reference may appear several times on sheet Input, but each of the reference numbers to be extracted from Sheet Input where it appears on sheet1 . The second problem is to extract the debit and credit values applicable to the extracted input reference numbers. It is easy if the reference number only appears once on the input sheet

    Your assistance in resolving this is most appreciated






    I have attached two workbooks. One showing the raw data and the other one where I have manually extracted the data
    Attached Files Attached Files

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If it is UNIQUE matches you want you can modify your row 2 formulas to
    =IF(COUNTIF(A$2:A2,A2)>1,"",VLOOKUP($A2,INPUT!A:J, 1))
    also suggest you have a defined name for the lookuprange so you don't waste resources on an entire column. Goto formulas>name manager>name it> in the refers to box copy this formula
    =OFFSET(INPUT!$A$1,1,0,COUNTA(INPUT!$A:$A),COUNTA( INPUT!$1:$1))
    then use
    =IF(COUNTIF(A$2:A2,A2)>1,"",VLOOKUP($A2,lookuprang e,1))
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Don

    Thanks for your help

    When using the if(countif .... together with the Vlookup, it is returning incorrect results -see items marked in yellow which only appear once on the input sheet but is being extracted several in a few instances. If it appears more than once on the input sheet, then it must be extracted the number of times it appears on the input sheet

    I have attached the workbook and have highlighted the incorrect results. It would be appreciated if you will have a look at this and amend your formula

    Thanks

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2015-03-28 at 10:34.

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You had the formulas messed up in the middle of the column. Use ATTACHED
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    ..here's my vba method of getting your data:
    Code:
    Sub getValuesFromInputSheet()
    
    [e2] = "=a2"
    [f2] = "=SUMIF(INPUT!A:A,e2,INPUT!F:F)"
    [g2] = "=SUMIF(INPUT!A:A,e2,INPUT!G:G)"
    
    zLastRow = [a1].CurrentRegion.Rows.Count
    temp = "e2:g" & zLastRow            'e.g. "e2:g142"
    [e2:g2].Copy Range(temp)            'copy formulas to range
    Range(temp) = Range(temp).Value     'convert formulas to values
    
    ActiveSheet.Range(temp).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlNo
    [e2].Select
    
    End Sub
    see attached .xlsm file

    zeddy
    Attached Files Attached Files

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    ..in my vba method, the data is in the same order as the Ref no appears in column [A].
    You could easily sort the returned data 'properly' if you wanted.

    zeddy

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Don

    Thanks for your help, much appreciated

    Howard

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for your code. It works perfectly


    Howard

  11. #11
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    See attached for another way
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Don

    There is no attachment per post #11

    Howard

  13. #13
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    trying again
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Don

    Thanks for the amended file

    It would be appreciated if you can amend the formula on the attached workbook. If the reference number appears more than once on sheet "Input" , then the reference numbers must be extracted on sheet1 as well as the applicable debit and credit values for eg Ref KLKOLISI appears twice on Input sheet and is only extracted once on sheet1

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files

Posting Permissions

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