Excel

[Super_Donut_Man]

Alright

 

I got a list that I am compiling using vlookups.

 

For some items have multiple lines of numbers, and accounts etc.

 

Right now it looks up the correct information for the first line, but it just repeats it for the remainder.

 

i.e.

something like this...

Vendor --------Invoice #--------- Acct#------ Amount

Vendor 1 ----- #123456-----------6007---------200

Vendor 1 ----- #123456-----------6008---------900

Vendor 1 ----- #123456-----------6009---------50

 

as opposed to what it coming out like this

 

Vendor 1 ----- #123456-----------6007---------200

Vendor 1 ----- #123456-----------6007---------200

Vendor 1 ----- #123456-----------6007---------200

[Super_Donut_Man]

Pretty much what I am trying to do is, export a file from our accounting system which contains a lot of useless information, and copy it to a sheet that (through vlookups) sorts through and pulls out the information that I need.

 

And this information would be put into other workbooks to supposedly make everyones life easier (except mine at the moment )

[Super Bass]

 

Alright I got a list that I am compiling using vlookups. For some items have multiple lines of numbers, and accounts etc. Right now it looks up the correct information for the first line, but it just repeats it for the remainder. i.e. something like this... Vendor --------Invoice #--------- Acct#------ Amount Vendor 1 ----- #123456-----------6007---------200 Vendor 1 ----- #123456-----------6008---------900 Vendor 1 ----- #123456-----------6009---------50 as opposed to what it coming out like this Vendor 1 ----- #123456-----------6007---------200 Vendor 1 ----- #123456-----------6007---------200 Vendor 1 ----- #123456-----------6007---------200

 

 

So you have multiple charge lines for each invoice?

 

Can you reference an individual identifier in the vlookup rather than the invoice number.

[Super_Donut_Man]

 

So you have multiple charge lines for each invoice?

 

 

Yes.

 

 

Can you reference an individual identifier in the vlookup rather than the invoice number.

 

 

That would be great, but there is not much to go off of, despite the amount of info in the report. I thought there might have been, but I couldn't find it.

 

If there was a way to do a lookup in a similar way as a nested if statement, I could see that working. Maybe an array formula would work?

[dabbler]

So is the desired list one with the total amount for each invoice or what?

 

If it is, try something like this: =SUMPRODUCT((C6:C10="#123456")*E6:E10) where you use a single line for each invoice # and the array in col C has all of the invoices and the array in col E has the amounts.

 

If this is not what you want, please describe it.

[Super_Donut_Man]

 

So is the desired list one with the total amount for each invoice or what? If it is, try something like this: =SUMPRODUCT((C6:C10="#123456")*E6:E10) where you use a single line for each invoice # and the array in col C has all of the invoices and the array in col E has the amounts. If this is not what you want, please describe it.

 

 

Not the total.

 

Basically I export a report from our accounting system that lists all the invoices posted in a certain period and to what accounts it effects (as well as a ton of useless info)

 

Some invoices have 1 line item, some have up to 10 or 11.

 

So the ultimate goal is to create a simplified report that will be used to provide a list of adjustments to each account automatically, cutting down the reconciliation time.

 

I have it all figured out once I get the simplified list (basically an array formula using sum(if(... )

 

I just get hung up on the multiple account invoices, and need a way around it.

 

I have a hell of a time explaining anything clearly, I hope this is clearer.

 

I do not want to make a pivot table, or anything complicated like that. Computer illiterate people will be using this.

 

Also, my manager said, "I want it to be 1)export 2) copy and paste, and nothing more." Almost there.

[Super Bass]

Yes. That would be great, but there is not much to go off of, despite the amount of info in the report. I thought there might have been, but I couldn't find it. If there was a way to do a lookup in a similar way as a nested if statement, I could see that working. Maybe an array formula would work?

 

Ok, just so I'm clear on what you want to do... are you trying to create a summary sheet showing the invoices by vendor with the related account and charges on the invoices?

 

This is way too similar to my job.

[WillPlay4food]

Make your own lookup then. For example, concatenate Vendor & Invoice & Account#. Looks like it'll be unique from the short list you provided above.

[Super_Donut_Man]

Make your own lookup then. For example, concatenate Vendor & Invoice & Account#. Looks like it'll be unique from the short list you provided above.

 

Hmmm.

This could work.

 

 

I think this idea would be better if there were a better report to go off of

[Super Bass]

Yep Just tell me its not restaurants

 

Haha, nah it's telecommunications.