Comparing 4 columns and return to another

[danieleus]

Hello all,

I'm new on this forum and i would like to ask you for a solution to "my problem" hopefully you will help .
i have 2 worksheets each of them containing a table with number of invoice quantity and currency, the table from the first worksheet was modified by deleting some rows and put in the second worksheet.
Unfortunately from the second worksheet somebody deleted a row with currency values and now i have to find out a way to put back the currency for each invoice.
Below is a small example column A number of invoice B quantity C currency
<table style="border-collapse: collapse; width: 162pt;" width="215" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <col style="width: 38pt;" width="51"> <col style="width: 62pt;" width="82"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 62pt;" width="82" height="17">69110469</td> <td class="xl66" style="width: 38pt;" width="51">800</td> <td class="xl67" style="width: 62pt;" width="82"> 1.548,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110469</td> <td class="xl66">600</td> <td class="xl67"> 1.515,20 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110466</td> <td class="xl66">180</td> <td class="xl67"> 1.823,40 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110466</td> <td class="xl66">280</td> <td class="xl67"> 1.825,20 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110463</td> <td class="xl66">200</td> <td class="xl67"> 605,23 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110462</td> <td class="xl66">400</td> <td class="xl67"> 1.709,04 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110467</td> <td class="xl66">120</td> <td class="xl67"> 867,84 </td> </tr> </tbody></table>The problem is that as you can see i have invoices with the same number the only solution i see is to compare the number of invoice and quantity from the second worksheet with this one and to return the corresponding currency value in a new column.
Could you please help with proper formula or with an advice.
Thank you

 

[BGY23]

Hi,
I assume you have a table with all the invoices and values in ? Only you say you deleted some rows and put them on the second sheet ? If you deleted them then formula's aren't going to help ?

Assuming you copied them or that at least you have a list of those invoices that are on sheet 2 on a separate sheet and with the values on.

You can limit your problem by "joining" the invoice number and quantity on sheet 1 and doing a lookup in sheet 2.

How?
1 insert a new column on sheet 1 at "A". Your Inv no., Qty and value should now be in Columns B,C and D.

2 In cell A1 write this formula =B1&C1. This will join your invoice number and your quantity.

3 Assuming your invoice no and quantity are in columns A and B in sheet 2 then in C1 write this formula <TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=311 border=0 x:str><COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11373" width=311><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 233pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=311 height=17 x:str="'=VLOOKUP(A1&B1,Sheet1!$A$1:$D$16,4,FALSE)">=VLOOKUP(A1&B1,Sheet1!$A$1:$D$16,4,FALSE)</TD></TR></TBODY></TABLE>

You will only have problems where the quantity on an invoice is duplicated.

Good luck

 

[danieleus]

Thank you very much BGY23 it worked perfect . Only few rows to check manually.
Regards