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

[Kenja824]

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

http://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.

 

[Kenja824]

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

http://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.

 

[3DDave]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

http://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.

 

[3DDave]

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?

 

[Kenja824]

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

http://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.

 

[Kenja824]

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

http://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.

 

[Kenja824]

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.

=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

http://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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmover]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

http://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.

 

[SkipVought]

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

Thanks for posting your soultion.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmover]

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.

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

http://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.

 

[pmover]

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.

 

[Kenja824]

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

http://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.