calling all Excel guru

[IHAVEAQUESTION]

Well, an Excel problem arises from my work and I believe there is a solution but I just don't know how.

Let's say 68001, 68002, 68003 are the sub-accounts of 68000, I want to be able to group them by the parent account (68000), I want my formula to do the following:

if(left(AccountNumber,5)=(68001, 68002, 68003),68000,0)

Well, that didn't work. I tried to put quote around each account number that still doesn't work. Any help?

 

[el-Capitan]

I am no expert and i am not 100 % clear on what you trying to achieve.

if(left(AccountNumber,5)=(68001, 68002, 68003),68000,0)

Those seem to be alternatives, but that will not work. Not very pretty, but you can nest the IF formula to test for alternative scenarios.

=IF(B1=68001,68000,IF(B1=68002,68000,IF(B1=68003,68000,B1)))

You are using LEFT incorrectly. it returns text only. I do not know the equivalent for numbers

 

[IHAVEAQUESTION]

Text is just fine. Because the column I am extracted the account number from looks like:

"68001 Global development"

So I really just want the first 5 characters from the left, unless there is a better way to extract it.

That's a lot of nested "IF"...some parent accounts have tens of sub-accounts and these sub-account numbers are all over the place, meaning they are not sequential or in any order. So using your method will be lengthy and confusing. Thanks for the suggestion though.

 

[el-Capitan]

LEFT is now understood. But i cannot get it to work with the nested approach.

You are correct, if you have dozens of different account numbers, then my solution is very impractical.

Sorry, cannot help :/

 

[IHAVEAQUESTION]

Can't believe there is not a similar formula as SQL's:

AccountNumber in (xxxxx,xxxxx,xxxxx)

Any other help?

 

[Homerboy]

Got a chunk of sample data? You can anonymize it of course.

 

[Binky]

Consider using a vlookup or index/match function. You could build a table of all master and sub accounts, and have your function pull the master account from there.

 

[PowerEngineer]

Try this:

=ROUNDDOWN(VALUE(LEFT(AccountNumber,5)),-3)

 

[sweenish]

It almost sounds like before we get into any formulas or even VBA, the spreadsheet itself might need some organizing, or like Binky said, a new table that you can reference.

 

[Puffnstuff]

Well you can nest up to 64 items in an IF function so it should work. Just remember to tell it what to do if true and if false in the dialog box. Once you get the parent cell established you can build a table or you can drag and drop the formula across the affected cells. You need to start with IF(D5=>68000,,), using D5 as the example cell containing the information. You can google office help and find the tutorial on the IF function pertaining to numbers like this.

 

[Blazer7]

=IF(AND(VALUE(LEFT(cellnumber;5))>=68000;VALUE(LEFT(cellnumber;5))<=68999);"68000";"0")

HTH

PS
Because of my local settings I have to use ; instead of ,
Just adjust to your needs

 

[Dulanic]

That or use a VLOOKUP and VALUE and place the values your grouping in another area...If you put the values in a named range you could do it like so...

=IF(VLOOKUP(VALUE(LEFT(AccountNumber,5)),LookupRange,1,FALSE),VLOOKUP(VALUE(LEFT(AccountNumber,5)),LookupRange,2,FALSE),0)