Vlookup without complete references HELP!

[CkeyAccountant]

Currently I need to take system reports and type them into excel to create financial statements. I would like to make this process a bit quicker by using vlookup to search for each item within the system output, but I have one problem. the system doesn't give me complete references. Here is an example.

40100 Sales
110 Pool Tables 10000.00
112 Pinball Games 5000.00
114 New Video Games 150000.00
118 New Music 75000.00
40100 Sales 240000.00

50100 Cost of Goods Sold
110 Pool Tables 7000.00
112 Pinball Games 2000.00
114 New Video Games 120000.00
118 New Music 50000.00
50100 Cost of Goods Sold 179000.00

So the reference for the COGS for Pool tables is 50100 110 and sales is 40100 110. the problem is the system only prints the Main account number as a header and footer. is there a way to vlookup cost of goods sold for pool tables ect without adding 50100 to all COGS lines?

 

[successone]

We use third party software to setup templates to parse reports when they are generated. Look at www.symtrax.com for a program called Compleo.

The "harder" way is to use a macro to copy the account number down using End Down (down arrow key) and coming back up the rows required.

 

[xenou]

If all your data is in column 1 you can just parse it in the adjacent column:

<img alt="screenshot" src="http:///northernocean.net/etc/mrexcel/20110718.png" />

In the above, B1 is a seed value that I typed in:
40100

Formula in B2 is:
=IF(ISNUMBER(LEFT(A2,5)),A2,LEFT(B1,5)&A2)

Another B2 formula you could use instead:
=IF(SEARCH(" ",A2,1)>4,A2,LEFT(B1,5)&A2)

You could probably use a similar strategy for breaking out the formula into account/amount columns for a pivot table or what have you:
In C2:
=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",50)),50))

In D2:
=VALUE(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50)))
The last fails on the header lines is all.

 

[CkeyAccountant]

The data is in 4 columns, one with the account 2nd with account name, 3rd with month total and last with YTD numbers (didn't type them out for the example)

 

[CkeyAccountant]

What I want to do is something like =vlookup(50100 110,SYSTEM OUTPUT!, 3,false) I just cant get that full reference :\ Unfortunately If I take the time append all the accounts so they have unique identifiers, I lose all the time I would have saved by doing this.

 

[bosco_yip]

...……A..…....…B...........................C............D.........E......F..........
..1...40100...Sales
..2...110……..Pool Tables…………...10000.00…50100….110…7000.00
..3...112……..Pinball Games………..5000.00……. ……..…112…2000.00
..4...114……..New Video Games...150000.00… ………..114…120000.00
..5...118……..New Music………….....75000.00…… ………..118… 50000.00
..6...40100...Sales.....................240000.00
..7...50100...Cost of Goods Sold
..8...110……..Pool Tables…………...7000.00
..9...112……..Pinball Games………..2000.00
..10..114……..New Video Games...120000.00
..11..118……..New Music………….....50000.00
..12..50100...Cost of Goods Sold..179000.00

1] D2 enter 40100 or 50100 (example herein enter 50100)

2] E2:E5 enter 110, 112, 114, and 118

3] F2 formula copy down to F5

=LOOKUP(2,1/(A$1:INDEX(A$1:A$50,MATCH(2,INDEX(1/(A$1:A$50=D$2),0)))=E2),C$1:INDEX(C$1:C$50,MATCH(2,INDEX(1/(A$1:A$50=D$2),0))))

Regards

 

[CkeyAccountant]

...……A..…....…B...........................C............D.........E......F..........
..1...40100...Sales
..2...110……..Pool Tables…………...10000.00…50100….110…7000.00
..3...112……..Pinball Games………..5000.00……. ……..…112…2000.00
..4...114……..New Video Games...150000.00… ………..114…120000.00
..5...118……..New Music………….....75000.00…… ………..118… 50000.00
..6...40100...Sales.....................240000.00
..7...50100...Cost of Goods Sold
..8...110……..Pool Tables…………...7000.00
..9...112……..Pinball Games………..2000.00
..10..114……..New Video Games...120000.00
..11..118……..New Music………….....50000.00
..12..50100...Cost of Goods Sold..179000.00

1] D2 enter 40100 or 50100 (example herein enter 50100)

2] E2:E5 enter 110, 112, 114, and 118

3] F2 formula copy down to F5

=LOOKUP(2,1/(A$1:INDEX(A$1:A$50,MATCH(2,INDEX(1/(A$1:A$50=D$2),0)))=E2),C$1:INDEX(C$1:C$50,MATCH(2,INDEX(1/(A$1:A$50=D$2),0))))

Regards

This problem with that solution is there are about 50 main accounts and I do the financial statements for 13 subsidiaries, so i would have to do that process around 650 times a month lol. and thus not really saving me any time :\ Trying to find a way to do this without having to modify the input data too much. Thank you for your reply though

 

[xenou]

What I want to do is something like =vlookup(50100 110,SYSTEM OUTPUT!, 3,false) I just cant get that full reference :\

What do you mean by "I just can't get that full reference"?

so i would have to do that process around 650 times a month lol. and thus not really saving me any time :\

I'm afraid I don't understand this either. You can't type a formula in Excel because it takes too long? {confused}

 

[CkeyAccountant]

Sorry let me clarify.
"so i would have to do that process around 650 times a month lol. and thus not really saving me any time :\ "

I was referring to this:

1] D2 enter 40100 or 50100 (example herein enter 50100)

2] E2:E5 enter 110, 112, 114, and 118

Sales and CoGS are only 2 of around 50 accounts I am dealing with. All refer to the same product categories. So if I need to perform steps (1) and (2) for each account, It will take quite a long time. That would be fine if I only had to do it one time, but I would have to do it once a month for 13 different reports, which adds up very fast.


Now for
"What do you mean by "I just can't get that full reference"?"
I mean that the full reference for Sales of Pool Tables is 40100 110
But each sales line only has the 3 digit division code and doesn't include the 5 digit main account code. Without both Main and Division I don't know how to refer to the right cell.

However if I made a new cell with both main and division. It would take a long time to format, thus defeating the point, as i'm trying to speed the process up.

I don't know if this is even possible, I was really just asking this forum to see what my options were.

I have one idea idk if there is a way to do it though...Before and after each main account it displays the account number, is there a way to use that to set the array? basically so if i was looking for sales of pool tables it would only look for a 110 between 40100 and 40100?

 

[shawnhet]

As a quick and dirty fix here, I normally do something like the following:

Insert two columns before column A and 1 row on top of 1,

Then in A2 paste the following =IF(C2>1000,C2,A1)
and B2 paste the following =CONCATENATE(A2," ",C2)

Fill both cells down to the bottom of your range.

Then I would recommend copying all you data and pasting the data as values to a second sheet and using that sheet as the reference for your Vlookup.

Cheers,