×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Im looking for some formula help please --- Again. lol

Im looking for some formula help please --- Again. lol

Im looking for some formula help please --- Again. lol

(OP)
To be clear, if this can be done by making VB code that will create a function to do the job, it would be fine. My original file has a lot of formulas and no coding but I will take what I can get here.

The attached excel file is version 2007 (I think).

I need a formula that will look at a cell on one tab and count how many in a specific column in another tab there are, based on whether they have the correct criteria in another column on that tab.

I could have handled this but it gets tough here. The information on the other column can be different possibilities.

Example criteria = s01proc1

I dont know if it will always be small case or not, so this cannot be case sensitive.

It will always start with the "s" followed by either 1 or 2 numbers, followed by "proc" and ending with either 1 or 2 more numbers.

If there is any more text after this, I dont want the cells counted. If they do not fit this criterial I dont want the cells counted.

-------

This will allow us to count only cells with the correct criteria and eliminate false info.

You will notice that if you scroll down on the SIM-1 tab. Around row 450, there will be two sets of duplicates. One has two cells with false criteria that should not be counted. The other has both cells with good criteria and both will be counted.

Thanks ahead of time. I have spent the last day creating a file to compare different outputs of information for my boss but this one problem has been killing me. I could add the IF statements to a formula to check a cell if it has the correct criteria, but I cant figure out how to make it work with counting only the cells with that criteria in the adjacent cells.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

I'm not certain I'm getting all of the requirements you're asking for. I'm thinking a COUNTIFS is the function you want to use. Using the spreadsheet you attached, if in Column P of tab COMPARE you are wanting to see how many times the text in the cells in column E (Spot#) occurs in Column E of tab SIM-1, you'd use the following:
=COUNTIFS('SIM-1'!A:A,UPPER(E4),'SIM-1'!A:A,LOWER(E4))

I don't think this is doing all of the things you want, but you can add more items in the COUNTIFS to restrict the counting.

RE: Im looking for some formula help please --- Again. lol

(OP)
Thought I had it and commented as much. Realized it is doing the wrong thing.

Our goal is to find any spots with that qualifying criteria are listed twice in the SIM-1 Tab. This was just counting inside the list I made.



Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
Sorry Zelgar, didnt see you had replied.


for an example.... in the COMPARE sheet, scroll down to row 257. You will find spot number 39K758. The formula that would be in P257 would look for how many times that spot number is found in tab SIM-1 column A, but it would only count the instances that have the correct criteria in column I.

The problem is the correct criteria can be many different options.

Hopefully this will help. I have attached to this comment a new version of the excel file. I added a list of all the options the criteria can match in column AL of the COMPARE tab.

Maybe it will be easier to just count the instances the spot number is found in the SIM-1 tab that has criteria in column I that matches any of the options in the new list?

Thanks for looking. I appreciate it.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Your selection criteria look rather complicated (and I have a hunch they will turn out to be even more so).  If so you will probably need VBA, to make use of its LIKE operator, along with an "Option Compare Text" declaration.  Warning: I have never used this in battle, having merely read about it.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Im looking for some formula help please --- Again. lol

(OP)
I appreciate the advice Denial.

Unfortunately I suck royally with VBA. lol



Right now, I am looking at this formula. It seems to work for its intentions. However I need to find a way to make the last part of it search the new list instead of giving it a specific text.

=SUMPRODUCT(--('SIM-1'!A2:A3000=E1477),--('SIM-1'!I2:I3000="s01proc1"))

I think I am burning out because this seems simple enough and I cant think how to do it. lol

It wouldnt work without the double dashes. What do those do?

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

"It wouldnt work without the double dashes. What do those do?"

They force a calculation of the expression within the parentheses, by returning 1 for TRUE or 0 for FALSE.

If you download your new workbook, with your new helper column, maybe we can come up with a solution.

It can also be written this way, which makes more sense to me...
=SUMPRODUCT(--('SIM-1'!A2:A3000=E1477)*('SIM-1'!I2:I3000="s01proc1"))

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
Just for the record, I think there is a glitch I am having with this site. When I start a new post and upload a file, it works. When I try to upload a file in a reply post, it acts like it is working and nothing appears.

Does anyone else have that problem?


Here is the URL it shows me...

Link


Thanks for pointing that out Skip. I tend to forget to double check to see if it loads like it is supposed to.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
And it worked right this time just to make me look bad. lol

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

So you have a solution?

Please post for us.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
I thought I had one earlier and realized it didnt do what I thought it did.

I now might have a solution, but it is using multiple helper columns. Unfortunately I am burnt out for the night and so strained I am staring at the monitor with one eye now. So I will check it again in the morning. I will try to make my trial version work with it and if it works I will post that soon.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
Here is the file. I have to punch out and running late to do so, so I wont be able to post an explanation how it all works tonight. You can dissect it if you wish. The file is hopefully attached this time. lol

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
Well it is not perfect. My boss would rather they did not have helper columns on the SIM tabs because different people will be copying and pasting info to those tabs and it is too likely they will paste info over top of the helper columns.

What I got will work to get things going, but I still need to figure out how to do this without those calumns.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Here's a solution without helper columns

=SUMPRODUCT(--('SIM-1'!A:A=E4)*('SIM-1'!I:I=INDEX('SIM-1'!I:I,IFERROR(MATCH(E4,'SIM-1'!A:A,0),0))))

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
Hey Skip

I am not sure if this is doing what I was looking for. I struggle with understanding SumProduct though for some reason.

When I added it, it changed the results so every spot had a 1.

Trying to figure it out, it seems to only search for the two columns, but I dont see where it calls for only those spot numbers with one of the "s01proc1 type of numbers to be counted. It allows for the spot number to be counted no matter what is in column I:I.

In column AL on the compare tab, there is a list of all the accepted proc IDs. If the cell in column I of the SIM-1 Tab is not in that list, I dont want it to be counted.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
Skip,


I just realized I didnt give you a very good file to work with as it didnt have any of the spot numbers that should have failed. lol Sorry.

A good example is if at the end of the list in Comapre column A, if you will add the spot number 65K8. That number is found in the SIM-1 tab but it has bad info in column I. So the formula would need to ignore counting that one.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

"The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible."

"To perform other arithmetic operations

Use SUMPRODUCT as usual, but replace the commas separating the array arguments with the arithmetic operators you want (*, /, +, -). After all the operations are performed, the results are summed as usual."

https://support.microsoft.com/en-gb/office/sumprod...

Quote:


In Excel, using two minus signs next to each other (--) converts "TRUE" to 1 and "FALSE" to 0.

Example : Let's take a simple logical condition : 3 > 2.

Apply this in Excel = 3 > 2 returns "TRUE". Using "--" (two minus signs together) with this condition returns 1 = --(3 > 2).

This method is commonly used in SUMPRODUCT formulas to convert conditional arrays that evaluate to TRUE/FALSE into 1/0. The SUMPRODUCT formula ignores non-numeric cells. Using the double minus signs technique, we can command Excel to convert TRUE/FALSE to 1/0.

https://www.listendata.com/2012/10/why-two-minus-s...

The "*" in the following is in place of a comma that would default to multiplication.

=SUMPRODUCT(
 --('SIM-1'!A:A=E4)
   *
   ('SIM-1'!I:I=INDEX(
                      'SIM-1'!I:I,
                      IFERROR(
                              MATCH(E4,'SIM-1'!A:A,0),
                              0)
                     )
   )
 ) 

RE: Im looking for some formula help please --- Again. lol

Discovered some things about your workbook that appears to be somewhat common in a workbook that has grown up like topsy.

I had my solution along with the column with your formula that referred to your helper columns et al. It took a long long time to calculate.

So I removed your formula and your helper columns and that helped a bit.

Then i decided to see how many Rows Excel considered to have "data" in them using a quick n dirty formula in the Immediate Window
?Activesheet.UsedRange.Rows.Count

...and I got a huge number on SIM-1

So I found the last row in Names and DELETED all rows below. Did the same on COMPARE.

Anyhow the File Size went from 5.7 MB to 90 KB.

Then I made each of your data lists, Structured Tables to limit the lookup/calculation range. So now this workbook calculates virtually immediately.

Your List       Table Name
SPOT#           tSpotNo
Name            tName
Operation Name  tOpName
Process List    tProcess List
 
The formula in Compare:
P4: =SUMPRODUCT
 (
--(tName[Name]=tSpotNo[@[SPOT'#]])*
  (tOpName[Operation Name]=
    INDEX(tOpName[Operation Name],
      IFERROR(MATCH(tSpotNo[@[SPOT'#]],tName[Name],0),0)))*
  (COUNTIF(tProcessList[Process List],
    INDEX(tOpName[Operation Name],
      IFERROR(MATCH(tSpotNo[@[SPOT'#]],tName[Name],0),0))))
 ) 

The SUMPRODUCT basically counts each SPOT# and Operation Name combination, (including Operation Names not in the Process List) but then, included in the SUMPRODUCT is an expression that takes the Operation Name associated with the SPOT#, and does a lookup/COUNTIF in the Process List. If it's not in that list, the combination is not counted.

You can see that the INDEX() expression appears twice, associating the Operation Name with the SPOT#. You could do this on the Compare sheet if so desired.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
3DDave
Thanks for that formula. It doesnt do exactly what I want, because it seems to return "1" for everything. I would only want it to count the quantity of each spot number that has info in column I on the SIM-1 list that is found in the accepted list in the compare tab on column AL. But in breaking it down and starting to understand it, I think I am beginning to see how to make it work.


Skip
I am unsure how this will work with my real file that has a lot more going on. I will email you.



pmover
I dont know if I am getting too old, or if it is just that I am juggling three other jobs at the same time right now, but those articles confused me. I will have to go back to them and understand them better when things settle down a little. Thanks.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

It's not my formula; I expanded Skip's formula to better show the way it worked.

RE: Im looking for some formula help please --- Again. lol

Ken, I'm guessing you're balking at using Structured Tables.

The major reason is to keep the Name and Operation Name list references equivalent.

The second reason is self-documentation: that is usually column headings are somewhat descriptive and can be much more helpful within a formula as opposed to A1 references.

But I do understand that there may be other more weighty reasons. I'll see what you have to say in your eMail.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
Skip

Also, I noticed that the SIM source comes with two columns with the same header name. The only difference is one is Name and the other is name. One letter us upper case. I asked about fixing this and am told they cant control it. Unfortunately both columns have info I use. lol

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Too bad Excel doesn't support regex except by VBA.

A 20 entry example is better.

The task appears to be to only count spots that have the correctly formatted OP and ignore spots that have matching spot number but not matching OP ?

A helper to identify correct op #s seems necessary.

Looks like someone unrolled a database that listed Spot#, OP number, Group and now want to undo the unroll.

Is the use to identify duplicated spot welds?

RE: Im looking for some formula help please --- Again. lol

(OP)
The use is to identify spots that are assigned multiple times. The "proc" info in that one column means a spot is assigned. Unfortunately one of the guys keeps track of old info by adding to that info. So if there is more added to the proc info it cant be counted.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
So this thing has just taken a bit of turn for the worse. My boss found a spot number that it is saying has an issue but is actually good. The problem is once again the spot being listed twice. Only the formula I am using finds the first entry instead of the one with the correct proc info. The first entry happens to have the word "delete" in the column were the proc info should be.

So not only do I need to find a way to count how many of each spot number there is (that has good proc info) but now I need to find a way for the other columns to look up the info only for spots that have the correct proc info as well.

In light of this, I felt it would be better to go through the file and delete any information that is not necessary to read in this comparison, and replace them with "x" in each cell and upload the real file. So you can see clearer what you are working with.

---------

The original formula I have been trying to figure is in columns "O" and "Q" on the Compare tab.

Ignore the formulas in column AK. They were just me testing things.



The new problem is in columns N and P. The formula looks for the spot number in the SIM tabs and returns the info from the ROB# column.

You will notice that spot# 33K828 in the SIM-1 tab is listed twice. In column I, one has the good proc number. The other has DELETE. Because the first one in the list has DELETE, the formula is reading that this is an error spot. This means that now I have to change these formulas to look for spot numbers with the correct proc info as well and return their info and not the info of the same spot number with bad info.

Yeesh this is turning into a nightmare. lol

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

(OP)
I think I solved the problem with it returning the first instance which was an error. Its not perfect but I think it will work well enough.

I was using vlookup to match the spot number and return the column that had the needed info. Which grabbed the first instance. I found on line a formula that uses INDEX and SMALL to get the nth instance. Then I nested that in a few IF's.

If the first IF is blank, then go to the 2nd instance. IF that one is blank, go to the 3rd and if that is blank, just give a blank.

If we ever have a case where the same spot is in it 4 times, well we will deal with it then and I will add a 5th.

Its a long and ugly formula, but you do what works sometimes.

CODE -->

=IFERROR(IF(INDEX('SIM-2'!$F$2:$F$3000,SMALL(IF($E8='SIM-2'!$A$2:$A$3000,ROW('SIM-2'!$A$2:$A$3000)-ROW('SIM-2'!$A$2)+1),1))<>"",INDEX('SIM-2'!$F$2:$F$3000,SMALL(IF($E8='SIM-2'!$A$2:$A$3000,ROW('SIM-2'!$A$2:$A$3000)-ROW('SIM-2'!$A$2)+1),1)),

IF(INDEX('SIM-2'!$F$2:$F$3000,SMALL(IF($E8='SIM-2'!$A$2:$A$3000,ROW('SIM-2'!$A$2:$A$3000)-ROW('SIM-2'!$A$2)+1),2))<>"",INDEX('SIM-2'!$F$2:$F$3000,SMALL(IF($E8='SIM-2'!$A$2:$A$3000,ROW('SIM-2'!$A$2:$A$3000)-ROW('SIM-2'!$A$2)+1),2)),

(INDEX('SIM-2'!$F$2:$F$3000,SMALL(IF($E8='SIM-2'!$A$2:$A$3000,ROW('SIM-2'!$A$2:$A$3000)-ROW('SIM-2'!$A$2)+1),3))))),"") 

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Quote:

You will notice that spot# 33K828 in the SIM-1 tab is listed twice. In column I, one has the good proc number. The other has DELETE. Because the first one in the list has DELETE, the formula is reading that this is an error spot.

This is a good test case. Tough one! I'm working on it.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

ok, i downloaded the file and will work on it tomorrow . . . my brain is depleted for today.

RE: Im looking for some formula help please --- Again. lol

Ken, you've got a real mess on you hands when you're getting data from an external source that has columns in varying order and headings I presume. If it were me, I'd write VBA to standardize the column order at a minimum.

Since I don't understand the output requirements and the necessary transform required, I can't suggest other input transformations, but I'd also be tempted to get ALL similar input data into one table right up front.

Another thing I might do in VBA, for instance for your latest INDEX & SMALL solution, is to write User Defined Spreadsheet Functions. They're small chunks of code that return one value given the requisite argument(s). Use them on you sheet like any other function. Much simpler to conceptualize and code than a procedure to accomplish everything from A to Z. I used to do this to get data from corporate rehosted tables, like given a Part Number, return the Nomenclature. Given a Part Number, return the Manufacturing Time etc.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
Hey Skip

Unfortunately getting the date on one spreadsheet is not an option. We have two different groups who need to communicate any changes to where weld spots are assigned if any changes are made and such. This spreadsheet I am making is the source that will compare their outputs and see if there are any problems. To make things more difficult, they dont use the same programs, so their output spreadsheet is not in the same order nor do they have the same headers.

My spreadsheet is meant to create one list of all possible weld spots, look for those weld spots in each of their outputs, then see if they are assigned by both groups. If they are assigned by one and not the other, it needs to show that. If they are assigned in both but not to the same station, robot and gun that needs to be shown.

For some reason I just cant get the hang of VBA coding. I have basically given up trying. I just dont have enough time to focus on it consistently enough for me to retain it. My plan is to get this excel comparison working well enough for now. They have someone in their employment who can do VBA coding, but they always have him on bigger projects. I will need to press them to get him to change this over someday.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Well I'm gonna dissect your latest solution to learn something about the SMALL() function.

Thanks for posting your soultion.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

Kenja284,

i read that you have a workable solution.

Regardless, i'm looking at the "original posted" workbook matter today . . . & not the others.

sorry, but i do not understand:

"However, it will only count the cells that have the correct info in their adjacent cell in column I in the SIM-1 tab."

what is meant by "correct info"?

thanks.

RE: Im looking for some formula help please --- Again. lol

"Correct info" is a list in the Compare sheet in column AS called Process List if I recall correctly.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Im looking for some formula help please --- Again. lol

(OP)
Yes, thanks Skip. I did not see a notification of someone responding earlier.

I am attaching my file that seems to work. Boss might have problems he wont tell me because I have spent so much time on it. lol This file has a lot of info replaced by X's so nothing important will be leaked.

On the Compare Tab, in column AY is a long list of possible PROC numbers. Over 10,000 it ended up being. The first two numbers in it range from 1 to 99. For each one of those, the number at the end ranges from 1 to 99. THen there are some extras added for possibilities they use 01 instead of 1, and 02 instead of 2 etc...

The only spots in the SIM tabs that we want to count are spots that has a proc number in column I that can be found in that list in the Compare tab.


My working solution was to create helper columns in the SIM tabs (which is not preferable but acceptable to make it work.
Column AD in the SIM page looks at the list on the compare tab.
Column AB will create an ID with the spot number and the Proc number together IF the cell in column AD is larger than zero.
Column AA gets the spot number back from the special ID.

Columns in the compare tab will only find info using the new spot number list. This removes spot numbers that dont have the correct proc numbers.

The COmpare tab allows us to filter to see only spots that have different assignments in different programs.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Im looking for some formula help please --- Again. lol

Thanks Skip.

So, the initial search in COMPARE Sheet, FORMULAS column determines a number of SPOT# found in SIM-1 sheet, column A. No Problem.

Please confirm that the second part returns only those SPOT# that have the same "Process List" found in SIM-1 sheet.

For example, for the data provided, there are 3 SPOT# that match SIM-1 Sheet, Column A, i.e. Cell P254.

The next part determines if the "Process List" in row 254 is the same for the 3 SPOT# found in Cell P254.

RE: Im looking for some formula help please --- Again. lol

(OP)
Sorry pmover. I dont know what it is, when you reply, I am not getting a notification.

If the spot number has a cell in the same row that has a proc# that matches any in the list in the COMPARE tab, it will count it as a good spot. If it doesnt, it will ignore it. If it has a matching proc# but also has other info in the same cell string as the proc# then it is still ignored. The cell with the proc# has to be an exact match of any in the list.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close