Excel Help

[rajncajn]

I'm currently working on a burn down spreadsheet/report that tracks and averages multiple tests' completion percentage by date. Each test has it's own spreadsheet and contains a various number of steps and a date in when each of those steps were completed. I want to be able to look at any specific date and pull an average percentage of all of the tests' progress at that time. The average part & how to pull the data I've already pretty much figured out. The problem is that each individual test must be open in order to calculate in the data tracker. I've attached a sample of what I'm working with. I need the Calcs tab to work if the Test 1 & Test 2 tabs are separate spreadsheets & not open.

 

 

Nevermind, found a solution

Edited September 30, 2021 by rajncajn
Found a solution

[rajncajn]

New problem. I've got a list of fittings in a column and need a count of each that match the first several characters. All of the fittings start with "PE " then a unit number, than a letter identifier. ex. PE 3421AA. I need to know how many PE 3421's I have in the column without having to have a separate list of each unique unit number. I know the formula =left(A1,7) will give me the "PE 3421" I'm looking for, I just don't know how to build it into a countifs formula. The result will go in an adjacent cell to each fitting number, so it will look like

 

 

My ultimate goal is each unit has to have 10% of the fittings tested, so I'll be building in a count of how many there are vs how many have been tested to result in a % of unit complete.

Here's one of the lists:

PE 3121EH

PE 3121EJ

PE 3121EK

PE 3121EL

PE 3221BU

PE 3221BV

PE 3221BW

PE 3221BX

PE 3221BY

PE 3221BZ

PE 3221CA

PE 3321A

PE 3321B

PE 3321C

PE 3321D

PE 3321E

PE 3321F

PE 3421Z

PE 3421AA

PE 3421AB

PE 3421AC

PE 3421AD

PE 3421AE

PE 3421AF

PE 3421AG

PE 3421AH

PE 3421AJ

PE 3421AK

PE 3421AL

PE 3421AM

PE 4121AC

PE 4121AD

PE 4121AE

PE 4121AF

PE 4121AG

PE 4121AH

PE 4121AJ

PE 4121AK

PE 4121AL

PE 4121AM

PE 4121AN

PE 4121AP

PE 4121AQ

PE 4121AR

PE 4121AT

PE 4231B

PE 4231A

[rajncajn]

19 minutes ago, LordOpie said:

I'm not good with excel, so just guessing, but a "sum" of a "xlookup"?

Sumif doesn't work because it's text mixed with numbers. Excel doesn't recognize the data in the cell as a number. The formula would be a countif. So, if I do a formula on that list that goes like

=COUNTIF($A$1:$A$47,"PE 3121*")

Excel will look in that column and count how many start with "PE 3121" and give me the count of "4." The problem is, the list I have is a VERY small example from one list. Some of the lists have 30-40 separate units with hundreds of fittings.

 

xlookup, I'm assuming you mean vlookup or hlookup? I prefer using index, but a vlookup or index and match might work.

[rajncajn]

@Big Country what do you think?

[Big Country]

21 minutes ago, rajncajn said:

Sumif doesn't work because it's text mixed with numbers. Excel doesn't recognize the data in the cell as a number. The formula would be a countif. So, if I do a formula on that list that goes like

=COUNTIF($A$1:$A$47,"PE 3121*")

Excel will look in that column and count how many start with "PE 3121" and give me the count of "4." The problem is, the list I have is a VERY small example from one list. Some of the lists have 30-40 separate units with hundreds of fittings.

 

xlookup, I'm assuming you mean vlookup or hlookup? I prefer using index, but a vlookup or index and match might work.

 

Look into XLookup (not necessarily for this, but in general) - they basically took some of the function of Index/match, paired it with the simplicity of Vlookup. You essentially define your lookup value, lookup range and return range - no longer do you need to have the lookup range be the left most column and no longer do you need to count how many columns over the return range is.

 

15 minutes ago, rajncajn said:

@Big Country what do you think?

 

Give me a minute to work on it - I think you are on the right track, likely need to nest the Left(Cell) into the COuntifs formula. I'll try build up a dummy sheet to test it out.

[stevegrab]

Looks like BC can help you with that, its a bit over my Excel abilities. 

[Big Country]

@rajncajn

 

If your list is in column A, you can place this formula in column B (or whatever column you want) and copy it down. This assumes your data is not formatted as a table:

 

=COUNT(IF(LEFT(A:A,7)=LEFT(A2,7),1,""))

 

Adjust the A:A and A2 references if your list is not in Column A

[rajncajn]

8 minutes ago, Big Country said:

@rajncajn

 

If your list is in column A, you can place this formula in column B (or whatever column you want) and copy it down. This assumes your data is not formatted as a table:

 

=COUNT(IF(LEFT(A:A,7)=LEFT(A2,7),1,""))

 

Adjust the A:A and A2 references if your list is not in Column A

I'm looking at it a bit more in-depth and I may have to attack it from a different angle. I just have too many variables. I do appreciate it though.

[Big Country]

22 minutes ago, rajncajn said:

I'm looking at it a bit more in-depth and I may have to attack it from a different angle. I just have too many variables. I do appreciate it though.

Let me know if I can help or if you need to bounce ideas off of someone

[rajncajn]

37 minutes ago, Big Country said:

Let me know if I can help or if you need to bounce ideas off of someone

I may send you something tomorrow after I mess around with it a bit more. I've got to figure out if the effort it will take to put it all together is worth what I'll gain from it.

[rajncajn]

16 hours ago, LordOpie said:

But you won't know that until after. Besides, i think it's almost always worth the effort because you learn something.

Someday I'd like to actually dig into excel instead of my limited knowledge

So i say, go for it

Believe me, I've spent many years with that attitude & it's served me very well. However, in most situations I am able to evaluate what is worth the time because I know my job, know the people I work with(who would be using it) and know the requirements very well. It was a good idea and might be useful in the future to learn how to do, but the time it will take to figure it out for this specific purpose, the very limited usage I would get from it, no assurity that it will be utilized and without much further work could produce misleading data results all make it not worth pursuing further. If I had nothing better to do that might be a different story, but I've got way too many irons in the fire as it is, one of them being a system that would make all of this obsolete anyway.

[Big Country]

1 hour ago, rajncajn said:

Believe me, I've spent many years with that attitude & it's served me very well. However, in most situations I am able to evaluate what is worth the time because I know my job, know the people I work with(who would be using it) and know the requirements very well. It was a good idea and might be useful in the future to learn how to do, but the time it will take to figure it out for this specific purpose, the very limited usage I would get from it, no assurity that it will be utilized and without much further work could produce misleading data results all make it not worth pursuing further. If I had nothing better to do that might be a different story, but I've got way too many irons in the fire as it is, one of them being a system that would make all of this obsolete anyway.

 

I'm actually doing something similar at my job - we're trying to retire some older reports and consolidate/simplify them, but at the same time, we are supposed to have a new system in place by end of Q1 next year (which likely means end of next year or early 2024 is the reality) that is supposed to replace all (or at least a very large majority) of our primary reports, so it's the balancing act of figuring out how much effort to put in now and where to focus it, knowing we likely will have more changes in the not too distant future.

[stevegrab]

1 hour ago, rajncajn said:

Believe me, I've spent many years with that attitude & it's served me very well. However, in most situations I am able to evaluate what is worth the time because I know my job, know the people I work with(who would be using it) and know the requirements very well. It was a good idea and might be useful in the future to learn how to do, but the time it will take to figure it out for this specific purpose, the very limited usage I would get from it, no assurity that it will be utilized and without much further work could produce misleading data results all make it not worth pursuing further. If I had nothing better to do that might be a different story, but I've got way too many irons in the fire as it is, one of them being a system that would make all of this obsolete anyway.

 

I was going to say sometimes a full fledged computer system is just much better at doing those complex things. We have a very complex full business system for industrial distributors, does everything but payroll. Our legacy product has some 40+ years of development (I've been at it over 30) many things exist only for a single or handful of users. The report writing tool that comes with our environment can be very useful, with both our users writing their own reports and our support/development team doing others. But sometimes the requirements are just too complex to get working right with the report tool and require a program to do it, especially when data is spread out over several files.

 

We've got some people at my office that are pretty good with Excel, I've learned some over the years but just don't use it enough to be proficient. Sometimes finding the right command is what is needed to start. 

[rajncajn]

44 minutes ago, Big Country said:

 

I'm actually doing something similar at my job - we're trying to retire some older reports and consolidate/simplify them, but at the same time, we are supposed to have a new system in place by end of Q1 next year (which likely means end of next year or early 2024 is the reality) that is supposed to replace all (or at least a very large majority) of our primary reports, so it's the balancing act of figuring out how much effort to put in now and where to focus it, knowing we likely will have more changes in the not too distant future.

I've been pushing for a software-based system since I've been in this position.  My opinion has been that the work we do is way more important than to be subject to the drawbacks of an excel spreadsheet and even that is a huge improvement over what they had before I got here. I'm finally now in a position where I have the resources to build the software,  now it's all about trying to prove it can be done and selling it's value to the customer. The latter will be the easy part. 

Edited March 24, 2022 by rajncajn

[rajncajn]

Big Country I've got another formula problem to solve if/when you've got the time. Actually typing out this post helped me solve the problem of getting the results, but I was still wondering if there was a more efficient way of doing it. Edit: well, strike that last. I'm only getting a result of "1" so still lookng for a workable solution.

This is a sample of my table

DOC NO	TR	% COMPL
12345-2-001	G	100.00%
12346-3-002	K	100.00%
12347-4-003	G	0.00%
12348-5-004	K	16.00%
12349-2-005	K	0.47%
12350-7-006	K	0.00%
12351-2-007	K	0.00%
12352-3-008	G	23.53%
12353-2-009	K	35.87%
12354-5-001	K	0.00%
12355-5-002	K	100.00%
12356-2-012	K	91.69%
12357-5-005	K	0.00%
12358-2-014	G	100.00%
12359-6-015	K	47.99%
12360-2-016	K	49.32%
12361-6-017	K	0.00%
12362-7-018	K	0.00%

 

What I need is to know, by TR, what Documents are at 100%. That part is simple. The kicker is that I need to exclude the documents that contain "-6-" or "-7-."

I know that I can use a COUNTIFS using the TR & % COMPL columns and I know that I can do a SUMIFS(S) or SUM+SUM+SUM or SUM(AND/OR of DOC NO to either include the "-2-","-3-" etc or exclude the "-6-" and "-7-" documents and get the results I'm looking for. I just wanted to know if there was a more efficient way of doing it. Right now I have:

=SUM(AND(COUNTIFS(Table[DOC NO],"<>*-6-*",Table[TR],"G",Table[% COMPL],1),COUNTIFS(Table[DOC NO],"<>*-7-*",Table[TR],"G",Table[% COMPL],1)))

Edited July 19, 2023 by rajncajn

[Big Country]

@rajncajn Real quick, FYI, my user name is Big Country - you are tagging a username of someone else that has it all as one word and lowercase so I'm not getting notifications on those tags.

I'm travelling home from Denver today so probably won't be able to look right away, but can you add in some additional helper columns to make the follow up steps a bit easier?

For example, add in a column with a formula to extract the middle number between the "-". There is a TEXTBEFORE and a TEXTAFTER formula that would do this, then you could more easily filter this column for 6 or 7 - Here's a link with some examples of doing this

Then you can either apply filters directly on the list (Filter % to 100% and filter the helper column to exclude 6 and 7) or if I am understanding the need, we can probably set up a Pivot Table to do this as well and give you the list of documents that meet the criteria.

The benefit of the Pivot Table is that it could be set up to show all the documents at 100% grouped by "TR", if that is a desired outcome.

When I am back home I will reach out to you if you still need some help or if this wasn't what you were looking for.

[rajncajn]

2 hours ago, Big Country said:

@rajncajn Real quick, FYI, my user name is Big Country - you are tagging a username of someone else that has it all as one word and lowercase so I'm not getting notifications on those tags.

Oh man, sorry about that. I'll take the tags out and keep in mind going forward. DMD should delete the other guy out of courtesy. 

2 hours ago, Big Country said:

I'm travelling home from Denver today so probably won't be able to look right away, but can you add in some additional helper columns to make the follow up steps a bit easier?

For example, add in a column with a formula to extract the middle number between the "-". There is a TEXTBEFORE and a TEXTAFTER formula that would do this, then you could more easily filter this column for 6 or 7 - Here's a link with some examples of doing this

Then you can either apply filters directly on the list (Filter % to 100% and filter the helper column to exclude 6 and 7) or if I am understanding the need, we can probably set up a Pivot Table to do this as well and give you the list of documents that meet the criteria.

The benefit of the Pivot Table is that it could be set up to show all the documents at 100% grouped by "TR", if that is a desired outcome.

When I am back home I will reach out to you if you still need some help or if this wasn't what you were looking for.

The Excel Forum actually came to the rescue. I ended up with something like this:

 

=SUMPRODUCT((--MID(Table[DOC],7,1)+0<6)*(Table[TR]=$E10)*(Table[% COMPL]=1))

 

I considered trying SUMPRODUCT, but never thought about using MID or the less than that way. Pretty clever.Thank you for offering the help anyway.