Pencil pushing Excel gurus, please enter

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
This functionality using INDEX / MATCH would be most excellent in reducing the amount of time I spend manipulating an Excel file.

I entered the formula and data exactly as shown in the example, and I can't get it to work. I hope it isn't some nerd playing a practical joke on me. The only thing that looks weird to me are the outside brackets. I've never seen those used in a formula before.


If%20cell%20contains%20one%20of%20many%20things.png
 

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
How about tell us what your end goal is for this formula?

isnumber just validates a number....true or false

I'm 99% you're an accountant/finance guy too. I'm using this on an export from our bank transactions. Currently, I have to manually code each withdrawal based on the description. I was hoping to use this to automate that process. For example, the description contains the text "PAYROLL", return "5164". 5164 is where we book our production salary/wages.

There are hundreds of line items each month that have to be manually coded. And many of these wire/payments repeat each month. So I was going to set up an index match.

Yes, I realize manually coding stuff is old school, but this legacy system is a huge piece of shit and does not generate any journal entries. We're moving towards an ERP in the next year that will make this task obsolete.
 

FESTER665

TCG Elite Member
TCG Premium
Apr 13, 2008
39,959
66,092
Streamwood
My biggest issue we have is that some of the bill of materials we receive here are like 900 lines long in excel, and some of the lines are the same part number and we try to count the amount of lines for each part number to get a final tally and its a pain in the ass.

My main goal would be to use a formula in that row that would let me know that there's 200 of part A, 210 of part B, 38 of part C, etc...

Anyone know if that's possible?
 

sickmint79

I Drink Your Milkshake
Mar 2, 2008
26,906
16,613
grayslake
How about tell us what your end goal is for this formula?

isnumber just validates a number....true or false

tokenize string in column b by spaces, look for any matching substring in e, populate value from f into c as a lookup.

this is probably not an ideal problem for excel for various reasons, one being what happens when you have 2 words that match instead of just one?

i don't understand how the search function will work given the set of values (things) as it seems like search wants a cell/value instead of a range.

where did the original spreadsheet/code come from?
 

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
tokenize string in column b by spaces, look for any matching substring in e, populate value from f into c as a lookup.

this is probably not an ideal problem for excel for various reasons, one being what happens when you have 2 words that match instead of just one?

i don't understand how the search function will work given the set of values (things) as it seems like search wants a cell/value instead of a range.

where did the original spreadsheet/code come from?

This is the website I found it on:

https://exceljet.net/formula/if-cell-contains-one-of-many-things
 

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
What systems are you looking at? We're looking like we're going to end up using SAP Business One at our place, but we do manufacturing so not sure if the ERPs you're looking at are totally different? :dunno:

I wish I could tell you, but for some reason they want us to keep it on the down low. I just started this job a couple weeks ago, so I should keep my mouth shut.
 

Chet Donnelly

TCG Elite Member
TCG Premium
Aug 19, 2004
18,382
38,887
I'm 99% you're an accountant/finance guy too. I'm using this on an export from our bank transactions. Currently, I have to manually code each withdrawal based on the description. I was hoping to use this to automate that process. For example, the description contains the text "PAYROLL", return "5164". 5164 is where we book our production salary/wages.

There are hundreds of line items each month that have to be manually coded. And many of these wire/payments repeat each month. So I was going to set up an index match.

Yes, I realize manually coding stuff is old school, but this legacy system is a huge piece of shit and does not generate any journal entries. We're moving towards an ERP in the next year that will make this task obsolete.
Yep...I'm in corporate finance. You can do this...with isnumber.

your formula will be "=isnumber(search("PAYROLL",A1))" A1 can be whatever cell you have the text in. This will give you a true or a false if the word payroll is in cell A1. Then you write an if statement along the lines of "=if(B1=TRUE,"5164","")" in another column.

Make sense?
 

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
My biggest issue we have is that some of the bill of materials we receive here are like 900 lines long in excel, and some of the lines are the same part number and we try to count the amount of lines for each part number to get a final tally and its a pain in the ass.

My main goal would be to use a formula in that row that would let me know that there's 200 of part A, 210 of part B, 38 of part C, etc...

Anyone know if that's possible?

It sounds like a pivot table would capture that data.
 

Eagle

Nemo me impune lacessit
Moderator
TCG Premium
Mar 1, 2008
63,904
4,732
Woodsticks, IL
My biggest issue we have is that some of the bill of materials we receive here are like 900 lines long in excel, and some of the lines are the same part number and we try to count the amount of lines for each part number to get a final tally and its a pain in the ass.

My main goal would be to use a formula in that row that would let me know that there's 200 of part A, 210 of part B, 38 of part C, etc...

Anyone know if that's possible?
Yes, it's definitely possible. Countif
 

Chet Donnelly

TCG Elite Member
TCG Premium
Aug 19, 2004
18,382
38,887
hiding profits so they don't have to pay taxes.

You don't even have to hide them. You just set up an entity in Singapore and funnel your revenue through that entity and pay their 10% corporate tax rate. You record all your expenses on your US entity and come up with a loss and pay no taxes in the USA.

Its really that simple and its legal.
 

sickmint79

I Drink Your Milkshake
Mar 2, 2008
26,906
16,613
grayslake

ok i got it to work. i was right on the search wanting a single value, the thing is the match goes through the list and does the search on every individual value. then you get an index if it's found and the other shit uses that as a lookup to put in your output value.

it will probably get slower if you have a large list, but hey, shit's probably fast enough for what you are doing. also it looks like if you put an object in the list twice, it simply uses the first value.

if you wanted a more dynamic string pattern matching thing, you could probably write your own little excel function with vb.

here's the key part you are missing:
This is an array formula and must be entered with Control + Shift + Enter.
 

Yaj Yak

Gladys
TCG Premium
May 24, 2007
122,223
87,859
Niche score of 2,363
You don't even have to hide them. You just set up an entity in Singapore and funnel your revenue through that entity and pay their 10% corporate tax rate. You record all your expenses on your US entity and come up with a loss and pay no taxes in the USA.

Its really that simple and its legal.

it was a joke.
 

blakbearddelite

I'm not one of your 'shit-hole' buddies!
TCG Premium
Jun 28, 2007
29,203
9,026
FL
Yep...I'm in corporate finance. You can do this...with isnumber.

your formula will be "=isnumber(search("PAYROLL",A1))" A1 can be whatever cell you have the text in. This will give you a true or a false if the word payroll is in cell A1. Then you write an if statement along the lines of "=if(B1=TRUE,"5164","")" in another column.

Make sense?

Except that I will have probably anywhere between 40-50 items that I will want to look for, not just cells that contain "PAYROLL".
 

Chet Donnelly

TCG Elite Member
TCG Premium
Aug 19, 2004
18,382
38,887
ok i got it to work. i was right on the search wanting a single value, the thing is the match goes through the list and does the search on every individual value. then you get an index if it's found and the other shit uses that as a lookup to put in your output value.

it will probably get slower if you have a large list
, but hey, shit's probably fast enough for what you are doing. also it looks like if you put an object in the list twice, it simply uses the first value.

if you wanted a more dynamic string pattern matching thing, you could probably write your own little excel function with vb.

here's the key part you are missing:
This is an array formula and must be entered with Control + Shift + Enter.
This is why I break them into multiple formulas if I can, and then just hide the columns that are meaningless. For whatever reason excel seems to work faster if you do this.
 
Old Thread: Hello . There have been no replies in this thread for 90 days.
Content in this thread may no longer be relevant. Consider starting a new thread to get fresh replies.

Thread Info