Excel Question

[Edaarin]

Okay I'm doing some ledger reconciliation in Excel. Can anyone help me out with the following?

I have a bunch of charge codes (each code is used severla times), and next to the charge code is the total expense. So basically I'm comparing four columns. I want to match up charge code to charge code (ie, code 4288 corresponds to 4288) and I want to see where the expenses are the exact same (ie, charge code 4288 for $300 appears in both columns).

Is there an easy way to do this?

[Kitsun]

Oh, oh, oh! I learned this once.

I think its called vertical lookup.

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

For the life of me, I can't remember exactly how its used... uhhh the help feature will explain it better than my hazy memory.

[Latrinsorm]

I don't understand how you have 4 columns. From what I understand, you have [charge code] and [expense]. What are the other two?

Anyway, highlight everything, then sort by [charge code] and then by [expense]. That's all one sort, which you should be able to do unless you have an amazingly old version of Excel.

If you're not allowed to move stuff around, make a fifth column with the following entry, assuming charge code is column A and expense is column B:
if(and(a1=a2,b1=b2),"Same","")
I'm pretty sure that "=" is what you want. If it doesn't work, try "==".

edited to disable smileys

[Edited on 7-5-2005 by Latrinsorm]

[Valthissa]

I think a lookup (V or H depending on how you've setup the spreadsheet) should work as Kitsun mentioned.

C/Valth <-- slave to spreadsheets these days

[Edaarin]

Sorry, I meant I have two columns each in two sheets. At the point that I was writing this thread I had manually gone through about 5,000 entries and was about ready to shoot myself in the head.

I was trying to figure out how to use VLOOKUP to get it to work, but the example we covered in training was for a totally different setup.

[Latrinsorm]

The way I said would only work if you did sort it by charge code. My bad.

I don't see how vlookup would be helpful in this situation at all, but maybe I'm just not understanding what vlookup does.

[Jazuela]

Can't you just do a global search within the workbook?

I'm pretty sure you can do that, and have Excel highlight each instance that the figure you're looking for appears, filtered to highlight only in the requested code types.

[Edaarin]

The workbook is set up something like this.

COLUMN 1
Code 1
Code 2
Code 3
Code 4

COLUMN 2
50.00
25.33
20.00
10.00

Next worksheet

COLUMN 1
Code 1
Code 2
Code 3
Code 4

COLUMN 2
50.00
25.33
15.00
10.00

In reality there are roughly 15 different charge codes for about 10000 line items (for the portion I'm responsible for anyway...there's 120 million lines total, unfiltered).

What I have to do is put a little 'x' in a third column for places where the Charge Code and the Expense are the same for both worksheets. In this case, code 1 2 and 4

[Latrinsorm]

If the charge codes always match up, do this in the third column:
=IF(B1=(Sheet1!B1),"x","")
assuming that Sheet1 is the name of the first sheet and that B1 is where the first expense is.

If the charge codes don't always match up and can't be made to always match up via sorting, I haven't the slightest idea how to make it work without making a program.

edit: Damned smileys. Why is ") a winking face anyway? Who does that?

[Edited on 7-6-2005 by Latrinsorm]

[Lookkin]

Or use a pivot table. is there a reason for the two separate sheets (eg same codes, difference code definitition) - it didn't appear that way in your original post.

If you copy/paste so that you're have one single sheet, select the whole data range and DATA/Pivot Table/Finish. From there you can select the critera to help you extrapolate your data in any way you want.

[Jorddyn]

If you're trying to match up two columns, you have to use an array function, which is a pain in the ass.

I recommend doing a vlookup in the third column to pull the amount that matches the code on the second sheet.

=VLOOKUP(A1,Sheet2!$A:$B,2,FALSE)

This will pull the amount if the code matches exactly, and an error if it doesn't.

THEN, in the fourth column

=if(a1=b1,"X","" ) (Get rid of that space between the " and the close paren - it was giving me smilies)

Jorddyn

[Jorddyn]

Originally posted by Jorddyn
If you're trying to match up two columns, you have to use an array function, which is a pain in the ass.

And, because I R Nerd, here's the array that would work.

=IF(SUM(IF(Sheet2!$A$1:$A$10000=A1,(IF(Sheet2!$B$1 :$B$10000=B1,Sheet2!$B$1:$B$10000))))<>0,"X","" )

Obviously, replace sheet names and cell ranges as necessary. Then, F2 to edit the cell, and ctrl-shift-enter to create the array.

Jorddyn

AND get rid of the space - damn smilies.

[Edaarin]

I ended up using an array similar to the one Jorddyn posted.

It would have saved me a lot of time if I had checked the PC at work rather than piss off the IT guyswho are brilliant with Oracle but can't use Excel for a damn. :fu:

[Suppa Hobbit Mage]

Or you could have IM'd me, I too am an excel GENIUS!

[Edaarin]

FYI running an array for 20000 cells completely PWNED my work station.

[Jorddyn]

Originally posted by Edaarin
FYI running an array for 20000 cells completely PWNED my work station.

Yea, but manually checking all 20,000 cells would have totally PWNED your brain. :)

Jorddyn

[Edaarin]

I manually checked about 5,000.

Coincidentally, yesterday was the first time in at least 5 years that I've taken anything like Excedrin, Advil, or Tylenol for headaches.

[Jorddyn]

Originally posted by Edaarin
I manually checked about 5,000.

Coincidentally, yesterday was the first time in at least 5 years that I've taken anything like Excedrin, Advil, or Tylenol for headaches.

If you want to IM me, I'll give you my e-mail address at work. Gotta save your brain cells :)

Jorddyn