Roger’s Excel Thread

[BTBaboon]

Originally Posted by rconverse

So is that actually needed or was it just him kind of getting a little to crazy with the example?

Also, if there are some cells that have an additional dash and then the "R1" or "R2", then he doesn't need that long IF(LEN(MID))) nested function. He can just use the LEN of the entire cell, which should make it a little easier and faster.

Yeah, I was a bit unclear on the R1/R2 as well.

By the way, if you don't mind me asking - what do you do for work? Assume it's something where you work frequently in excel (obviously )

[scrock25]

Just fuck her right in the pussy.

[rconverse]

Originally Posted by BTBaboon

Yeah, I was a bit unclear on the R1/R2 as well.

By the way, if you don't mind me asking - what do you do for work? Assume it's something where you work frequently in excel (obviously )

I work in my company's inventory management department. I used to do data conversions on defined contribution plans, so I've been using all kinds of analytical tools for a decade or so.

How about yourself?

[Arby]

Originally Posted by scrock25

Just fuck her right in the pussy.

Really? In the excel thread?

[scrock25]

That's the point

[daveshookme]

Originally Posted by Arby

Really? In the excel thread?

We're a dignified, sophisticated people here!

[BTBaboon]

Originally Posted by rconverse

I work in my company's inventory management department. I used to do data conversions on defined contribution plans, so I've been using all kinds of analytical tools for a decade or so.

How about yourself?

Gotcha, you are certainly a function guru!

I'm in finance, FP&A and recently took on some finance IT systems management. Been trying to migrate 40 site's ERP systems into a division wide data warehouse

[daveshookme]

Originally Posted by BTBaboon

Gotcha, you are certainly a function guru!

I'm in finance, FP&A and recently took on some finance IT systems management. Been trying to migrate 40 site's ERP systems into a division wide data warehouse

https://www.youtube.com/watch?v=Ccoj5lhLmSQ

[DIDriveOut2SPAC]

Originally Posted by rconverse

Do you mean R1 and R2 as in there are sometimes 1 character after the dash and sometimes 2 characters after the dash?

If so, why not use the LEN function to check for that?

=IF(LEN(TRIM(MID(H3, FIND("-", H3, 1)+1, 40)))<2, "Formula for 1 character", "Formula for 2 characters")

Also, is this just a static 6 that you're adding out of nowhere?



Where the heck does that come into play?

Thanks for the help guys

I administrator a construction project that's being built currently. We receive product submittals by specification division in sequence. They go by division number and then what's being submitted

12 34 56-01 product data
12 34 56-02 shop drawings

And so on

Sent from my Pixel using Tapatalk

[rconverse]

Originally Posted by DIDriveOut2SPAC

Thanks for the help guys

I administrator a construction project that's being built currently. We receive product submittals by specification division in sequence. They go by division number and then what's being submitted

12 34 56-01 product data
12 34 56-02 shop drawings

And so on

Sent from my Pixel using Tapatalk

This doesn't exactly tell me where that 6 comes from or why it's needed.

In your initial post, you as for help converting...

This...

12345-01
12345-02

To this...

12 34 56-01
12 34 56-02

My question is whether or not you really want that 6 to be included. It kind of comes out of nowhere. That said, if the solution is working, that's all that matters.

[DIDriveOut2SPAC]

Here is the real world example I am talking about

[rconverse]

Originally Posted by BTBaboon

Gotcha, you are certainly a function guru!

I'm in finance, FP&A and recently took on some finance IT systems management. Been trying to migrate 40 site's ERP systems into a division wide data warehouse

Data warehousing and BI are seriously good fields to be in right now.

[rconverse]

Originally Posted by DIDriveOut2SPAC

Here is the real world example I am talking about

Okay, let's take the very first one...051200-14.

Are you looking to see...

05 12 00-14?

Or are you looking to see something different?

[DIDriveOut2SPAC]

Originally Posted by rconverse

Okay, let's take the very first one...051200-14.

Are you looking to see...

05 12 00-14?

Or are you looking to see something different?

Thats exactly what I want it to look like, yes

[rconverse]

Originally Posted by DIDriveOut2SPAC

Thats exactly what I want it to look like, yes

Okay, that's not a problem.

What's the whole -R1 & -R2 thing? I didn't see any examples on your image. Are you saying that there are records in that column that may have an additional dash and characters after the second dash? If so, that can be accomplished as well.

[daveshookme]

Roger's your man for excel help. I've all but given up on trying to help anybody else in here. He'll usually beat me to it and with a much cleaner answer

[rconverse]

Almost forgot about this...

TO convert 051200-14 to 05 12 00-14, you can use:

=LEFT(A2,2)&" "&MID(A2,3,2)&" "&RIGHT(A2,5)

If you can post the other variations, I can write a workaround for those.

[BTBaboon]

Originally Posted by rconverse

Almost forgot about this...

TO convert 051200-14 to 05 12 00-14, you can use:

=LEFT(A2,2)&" "&MID(A2,3,2)&" "&RIGHT(A2,5)

If you can post the other variations, I can write a workaround for those.

Not doing a full backread, but what was up with the random '6' before?

I had to put 2 MID functions in mine on the last page because of it, much smaller formula without it

[rconverse]

Originally Posted by BTBaboon

Not doing a full backread, but what was up with the random '6' before?

I had to put 2 MID functions in mine on the last page because of it, much smaller formula without it

I don't believe he's said. I think he accidentally messed up the first part of his example. I think he thought he put...

123456-06
123456-07

But he really didn't so it just looked like a random 6 to us. I'm actually interested to find out.

[DIDriveOut2SPAC]

Originally Posted by rconverse

Okay, that's not a problem.

What's the whole -R1 & -R2 thing? I didn't see any examples on your image. Are you saying that there are records in that column that may have an additional dash and characters after the second dash? If so, that can be accomplished as well.

Sorry for my late response.

So lets use 051200-14 as the example again. Lets say that number is product data for me to review. If I review it and I don't accept the product they submitted, I will mark the corrections and return it revise and resubmit.

When they resubmit that product data for review again it will have the same number 051200-14 but it will be revision 1. Therefore for logging purposes this would be submitted as 05 12 00-14-R1.

[DIDriveOut2SPAC]

Originally Posted by rconverse

I don't believe he's said. I think he accidentally messed up the first part of his example. I think he thought he put...

123456-06
123456-07

But he really didn't so it just looked like a random 6 to us. I'm actually interested to find out.

You are correct.

I attached a better example and i will explain the process:

This is our submittal log for construction Divison 31. Division 31 in construction has to do with Earthwork on the building site.

When we bid the project we also have a index of specifications that the Construction Manager needs to adhere by. So here in Div. 31 the CM is submitting his means and methods of working, the products hes going to use and the calculations, etc. he is required to provide before doing work (for mine the the engineers review). So that number that is confusing you is generated by Division / Section in the Division / Subsection if necessary / Order of submission (this helps the CM keep a steady timeline for work and so things don't get accidentally budged in line)

So lets take the first one on top:

Division: 31

Section: 20

Subsection (null): 00

Order of Submission: -01

= 31 20 00-01

and if I don't accept it, then they revise and resubmit: -R1

31 20 00-01-R1 <---- if need be

I was away when a younger colleague of mine set this up and he didn't include the spacing, which we are all use to and is easier to find things quickly and efficiently.

I got the formulas for date of return and review time to automatically generate from the dates that we input so I'm not THATTTT big of an idiot. i just couldn't figure this one out.

The help is appreciated and I will test these out when I get some free time.

[rconverse]

Here you go...Pretty much allows for unlimited re-submissions.

=IF(LEN(A2)=9, LEFT(A2,2)&" "&MID(A2,3,2)&" "&RIGHT(A2,5), LEFT(A2,2)&" "&MID(A2,3,2)&" "&TRIM(MID(A2, 5, 40)))

[Arby]

Hey All

Is there a fairly simple way - without changing the base formula - to get Excel to replace "#DIV/0" (aka formula divides by zero) with something else...preferably "N/A"?

[daveshookme]

Originally Posted by Arby

Hey All



Is there a fairly simple way - without changing the base formula - to get Excel to replace "#DIV/0" (aka formula divides by zero) with something else...preferably "N/A"?

You could do a conditional format, but that's going to be a bitch. I'm almost positive there's a setting somewhere in the excel options I just don't know exactly where it is.

[BTBaboon]

Originally Posted by Arby

Hey All

Is there a fairly simple way - without changing the base formula - to get Excel to replace "#DIV/0" (aka formula divides by zero) with something else...preferably "N/A"?

=iferror(Your Formula that divides by zero,"N/A")

You can replace N/A with whatever you want

[Arby]

Originally Posted by BTBaboon

=iferror(Your Formula that divides by zero,"N/A")

You can replace N/A with whatever you want

Does that involve adding =iferror into each cell with the formula?

Sorry, bit of an Excel simpleton

[BTBaboon]

Originally Posted by Arby

Does that involve adding =iferror into each cell with the formula?

Sorry, bit of an Excel simpleton

Yes, so if your formula is currently =A1/B1; the new formula would be:

=iferror(A1/B1,"N/A")

Ensure you have a comma after B1 and ensure the N/A is surrounded by quotes. Then you can just drag the formula down to all cells in the column

[rconverse]

You could also avoid the error with an IF statement.

=IF(B1=0,"N/A", A1/B1)

You could also use the NA function, which I think is easier to deal with than the actual text of "N/A".

=IF(B1=0,NA(), A1/B1)

[Arby]

Originally Posted by BTBaboon

Yes, so if your formula is currently =A1/B1; the new formula would be:

=iferror(A1/B1,"N/A")

Ensure you have a comma after B1 and ensure the N/A is surrounded by quotes. Then you can just drag the formula down to all cells in the column

Originally Posted by rconverse

You could also avoid the error with an IF statement.

=IF(B1=0,"N/A", A1/B1)

You could also use the NA function, which I think is easier to deal with than the actual text of "N/A".

=IF(B1=0,NA(), A1/B1)

hmm - thank for these suggestions! Only trouble is that formulas are already entered. I likely wont be able to adjust for this go round, but Ill definitely use this in the future

Its a pretty huge doc

[BTBaboon]

Originally Posted by Arby

hmm - thank for these suggestions! Only trouble is that formulas are already entered. I likely wont be able to adjust for this go round, but Ill definitely use this in the future

Its a pretty huge doc

Are all the formulas in the same column, referencing two adjacent columns?