# Thread: Formula to extract ref numbers

1. ## 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

2. Hi Howard

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

zeddy

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

4. 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))

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

6. You had the formulas messed up in the middle of the column. Use ATTACHED

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

8. 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. Hi Don

Thanks for your help, much appreciated

Howard

10. Hi Zeddy

Thanks for your code. It works perfectly

Howard

11. See attached for another way

12. Hi Don

There is no attachment per post #11

Howard

13. trying again

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

#### Posting Permissions

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