VLookup...Wrong Formula?

**Tom K** · 12-24-2007, 11:24 AM

I have 2 Workbooks. One is invoice data extracted from SAP and the other is a workbook I have to send to the client (has about 10 worksheets containing 5-10 different branches in each worksheet). The layout of the one I send to the client can not change so I am trying to import the values form the 1st workbook (SAP) into the 2nd workbook. What I do now is copy the first 4 columns of data for each branch and paste it into the 2nd workbook.

I was playing with a VLookup formula but I don’t think that is the correct formula. I need the second workbook to be populated with all the invoice info (Number, Type, Date, Balance) that appears in the raw data workbook.

=VLOOKUP(H14,'[abcbank raw data sample.xls]Sheet1'!$A:$A+'[abcbank raw data sample.xls]Sheet1'!$A:$E,4)

H14 is the branch number on the client sheet (I cut the column containing the branch # on the raw data and inserted it before column A).

Raw Data From SAP

Please Login or Register  to view this content.

Workbook to send to client

Please Login or Register  to view this content.

Let me know if you need a better explanation.

Thanks.

**NBVC** · 12-24-2007, 01:14 PM

Try this formula

Please Login or Register  to view this content.

copied down and across as far as needed.

**Tom K** · 12-24-2007, 04:02 PM

Thanks NBVC. This is close. I made H14 Constant since that is my branch # I am searching for. When I copy across it works but when I copy down it returns only the values for the 1st invoice from the list. For branch # 2279 there are 6 invoices that I need to copy over. Can the VLookup do that or does it only return the first value in it's search?

=VLOOKUP($H$14,'[abcbank raw data sample.xls]Sheet1'!$A:$E,COLUMN(B$1),0)

Attached is a word doc of two of the excel sheets. The 1st page is the raw data and the 2nd page is the form it needs to flow into.

**davesexcel** · 12-25-2007, 07:39 AM

Check this filter exampe
http://www.excelforum.com/attachment...4&d=1173865150

**Tom K** · 12-26-2007, 10:46 AM

davesexcel- That is an interesting macro. I think I can use that for another spreadsheet I have, but it will not work for this one. I have to have the data put into specific cells so I can't just produce a list.

**daddylonglegs** · 12-26-2007, 11:29 AM

This is not a simple exercise using formulas but you could use something like the attached, based on your data.

Note: formula in B3 is

=IF(COUNTIF(Sheet1!$A$1:$A$24,$A$3)>=ROWS(B$3:B3),INDEX(Sheet1!B$1:B$24,SMALL(IF(Sheet1!$A$1:$A$24= $A$3,ROW(Sheet1!B$1:B$24)-ROW(Sheet1!B$1)+1),ROWS(B$3:B3))),"")

which is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER

which is copied down and across

**Tom K** · 12-27-2007, 11:01 AM

{=IF(COUNTIF('Raw Data'!$A$1:$A$24,$A$3)>=ROWS(B$3:B3),"INDEX('Raw Data'!B$1:B$24,SMALL(IF('Raw Data'!$A$1:$A$24=$A$3,ROW+'Raw Data'!B$1:B$24)-ROW('Raw Data'!B$1)+1),ROWS(B$3:B3)))","")}

I set up my spreadsheets to look exactly the same as the sample but my formula does not work. When I put in the correct # in B3 (the cell that the formula is matching) it displays the formula itself (like if the cell was formatted as a text, which it is not). When I type in an incorrect number in B3 it will display correctly blank.

Any help would be appreacited. If there is anyone I could talk to that would be even better. I'll send a pm with my phone # or vice versa.

**Tom K** · 12-28-2007, 03:57 PM

bump...Any additional help would be appreciated. I am confident this formula will work, I just need help understanding it.

**Dean England** · 12-28-2007, 04:32 PM

If you compare your formula to daddylonglegs formula you will notice that you have placed a couple spare " in your formula and a few other errors. Maybe if you correct those the formula will work.

Dean

**NBVC** · 12-31-2007, 11:40 AM

Originally Posted by Tom K

{=IF(COUNTIF('Raw Data'!$A$1:$A$24,$A$3)>=ROWS(B$3:B3),"INDEX('Raw Data'!B$1:B$24,SMALL(IF('Raw Data'!$A$1:$A$24=$A$3,ROW+'Raw Data'!B$1:B$24)-ROW('Raw Data'!B$1)+1),ROWS(B$3:B3)))","")}

I set up my spreadsheets to look exactly the same as the sample but my formula does not work. When I put in the correct # in B3 (the cell that the formula is matching) it displays the formula itself (like if the cell was formatted as a text, which it is not). When I type in an incorrect number in B3 it will display correctly blank.

Any help would be appreacited. If there is anyone I could talk to that would be even better. I'll send a pm with my phone # or vice versa.

You also have a syntax problem at the "ROW+'Raw Data'!B$1:B$24) location. The + should be an opening parenthesis....

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

**Tom K** · 12-31-2007, 04:05 PM

I copied the formula from NBVC and it partially work for me. The biggest problem is I don't totally understand the formula. I attached the spreadsheet. If anyone can look at it and do the first 3 branches on the 2nd spreadsheet then I should be able to figure it out.

Also, you see my sum formula shows #VALUE! for the unpopulated rows. If you know how to fix that please let me know.

Thanks.
Tom

**NBVC** · 12-31-2007, 07:39 PM

See attached...

I only fixed part of the first sheet.

Note: Copy the formula in B3 to the first row in column B of each other branch group. Then adjust the 2 occurances of the ROWS() part of the formula in the cell of column B to coincide with the row you are in...for example. Once you copy the formula from B3 to B14....you will have to change the 2 occurances of ROWS(B$3:B3) to ROWS(B$14:B14).

Then you can confirm the formula with the CSE keys and copy down the group and across to column E.

Repeat this for all Branch Groups.

Note: The formula has been updated to look in column A to find the last Branch # up to the current row with the function LOOKUP(10^10,$A$1:$A3) at B3. This is how it determines the branch to look up for matches. Also note that I used a range of up to Row 200...you may have to adjust that if you have more data in your Raw Data sheet.

So now the formula in B3 is:

Please Login or Register  to view this content.

Next, I changed the formula in column G to Sum(E3,F3) which ignores text entries or blanks. This formula needs to be copied down and to the other groups.

NOTE 2: The large formula is resource intensive....the more of these you have and the larger the range to look in, the more likely you will experience slow down in computer performance....

Hope this helps...

Good Luck and Happy New Year!!!!!

**Dean England** · 01-01-2008, 12:55 PM

Tom,

I was playing with your file last night and noticed a couple of things that I thought might help you. I notice you got an answer from NBVC, but I'll just add to this since I spent time on it. I had worked out a formula much like NBVC’s formula; but his is much better, so I went with his.

In several of the accounts I noticed that there were more items than you had provided for in your printout sheet, so I added an item total in the total column of each account, which gives a count of the items in your Raw Data Sheet for each branch. The formula is:

=COUNTIF('Raw Data'!$A$1:$A$200,B3)

for the first category and copied down to each Branch Total line. I then added a helper column in Column A to give you a count of each item. This helper column can be placed anywhere on the sheet or can be hidden. The formula is:
=IF(C3="","",ROWS($1:1))

and copy down to the end of the category. When you move the formula to the next category you will need to change the row numbers to the number of the starting row and copy down to the end of that category. The combination of the two formulae will show if your missing any items or not. It’s a bit difficult to explain, but I’ve attached a copy of your sheet with the formula from NBVC and my changes. If this helps at all and you need any additional info come back.

Dean

**Tom K** · 01-02-2008, 02:16 PM

Perfecto!!!!!

Thanks NBVC. You saved my project. Your changes and explanation are working perfectly. I am indebted to you now.

Dean England, Thanks for your input. The formulas for safety checking worked as well. I did not even think to automate my safety checks, heck I didn't even think too much about validating my data. You triggered the light bulb in my head.

What a better way to start the new year than figuring this out. This will end up saving one of my team members 520 hours a year (2 hr/day)... now this spreadsheet should only take her about 1-5 minutes to complete.