Formula help please

[Kenja824]

I cant seem to figure out which formulas to put together and get them to work for this.

In the attached files there is a range of words. (Just threw these words together for the sample)

In the yellow cell, I want to add a formula that will look for the word in A1 in A5:A14.

it might now always be there. If it does find it however, I then want it to look in that particular row for the word "God". If it finds that word in that row, then it will return "God". If it either does not find the first word in A1, or does not find the word God in its row, it can return a simple period. "."

I like to use periods because they dont draw attention and they let me know their is a formula in that cell if it doesnt find what it is looking for.

The reason I want this...

In my job, I often have to run what is called a CSV file. and it will list hundred to thousands of weld spots for the job I am doing. Every one of those spots have a bunch of information that will be pulled from their attributes.

Recently I have learned that there are spots that will give deceiving information but they will always have a certain anagram in their row. I typically paste the CSV files information into a prepared excel document that retrieves all kinds of info I need. I want to add this formula to it so that it will automatically let me know if one of these spots are in the job.

Thankyou for any effort in trying to help with this.


Sorry. For some reason I keep having problems uploading files here lately. Here is the URL they listed...

https://files.engineering.com/getfi...580-b0f8-6229323ec89a&file=excel_formula.xlsx

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]

Hey Ken,

Sounds like Elijah vs the priests of Baal on Mount Carmel!

=IF(EXACT(INDEX($E$5:$E$14,MATCH(A1,$A$5:$A$14,0),1),"God"),"God",".")

You need the EXACT() function to differentiate the capitalization.

This will only work if that particular value is the only value in that column for that row. But since I can't see your data in toto, can't say that it's a solution for you.

Go get 'em, tiger.

eMail me.

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

 

[Kenja824]

Thanks Skip

The problem here is that your formula seems to give the specific column to look in for "God". In the real job I am making this for, I wont know what column the info I need will fall in. So the formula needs to find the first part of the information in column A, then look through the entire row for the second part of the information.

I just ran two CSV files today. One for an old spot file and the other for a new spot file. The column that I would expect the info I needed to be in was different on them. In one it came out on AL and the other it was on column AP.

Also, if EXACT is not necessary, I wont need it in the formula. I dont care if it is capitalized in this case. The actual information is to look for the letters SFZ in a cell. Typically the cell will have something like RSW-SFZ but I dont know if it will always have the same exact letters. So I just want to look for any cell that contains SFZ.

What I have going is a a rather large excel file that has a lot of tabs in it. I will export csv files of the old and new spots for each style of vehicle I am working on. Then copy and paste them into tabs on the excel file. Then the main tab will have a ton of formulas that will compare the different tabs and look for differences. They will tell me whether a spot has moved its location, whether a new spot was added or an old one was deleted. It will compare the information from other tabs I fill as well, looking for what template each spot is on etc...

I didnt want to add a real file here for two reasons. 1) I dont want anyone getting off track asking why I am doing this or that and why I use certain formulas when I am just looking to add one formula. 2) I dont know if this information would be considered sensitive in GMs eyes or not. THey can be very picky about their secrecy in their product.

We have a younger guy here who knows some coding. He is supposed to try and turn this file into using VB Code instead of formulas, but he never seems to get around to it. I have a feeling it might be too much for his expertise and he has too many other things to do than to spend too much time trying to figure this one out. lol

I will definitely email you.

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]

"So I just want to look for any cell that contains SFZ"

That can be no cell, one cell or more than one cell?

If more than one cell, then can your list or table be sorted?

With a helper column having a FIND() function to look for your substring, which can be entered or selected in Row 1, each row containing your search string can return a value greater than zero and if those rows can be sorted to the top, you have all the occurrences.

A formula like this in the helper column...
=IF(IFERROR(FIND(G$1,E5),0)>0,1,0)
...where G1 contains the sub string and column E contains the weldspot string.

Are we getting any closer?

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

 

[SkipVought]

Don't know how I missed this...

"So the formula needs to find the first part of the information in column A, then look through the entire row for the second part of the information."

First, is there only one row found in column A?

Then where does the second lookup (substring I'm guessing) come from that will be embedded in an unknown column or columns in that row?

It's all muddy to me.

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

 

[3DDave]

It's more like a beginners first effort to learning VBA, but since I don't have any idea what the data or goal looks like I can only point to code that would be useful to open both a file of data and a file of "anagrams" (they don't look like anagrams.)

To find the match:

https://www.automateexcel.com/vba/instr-find-text/#case-insensitive-instr-test

To read data from a text/csv file:

https://www.automateexcel.com/vba/read-import-text-file/

Look for the section "Reading text files in arrays"

In the latter section, duplicate the part that reads the text file into an array to read the anagram file into its own array and then, for each row/entry/ one can do the INSTR() comparison and replace the anagram with whatever markout one wants.

One can loop through each line in the text array (the "x" index) and use a new index, "z", to loop through the anagrams array to check for matches.

 

[Kenja824]

Hey Skip

You must be on stealth mode. I didnt get an email that either of your responses came through. But I got an email for Dave. Whats with that? lol

Anyway

The formula will look at one cell for what it needs to find.

It will look for that in column A

Column "A" will hopefully always have the list of spot numbers. It is possible that column A can have duplicate numbers, but it doesnt matter. Any duplicates will have the same information to pull. So if it looks for one and finds one, that is good enough.

If it finds the number it is looking for in Column A, it will then look through that particular row for any cell that contains the specific text we are looking for. In the example it was "God" but in the real job it is "SFZ".

If it finds that text in any of the cells in that row, it will return that text "SFZ".

Not sure if I answered your question in this. I was unsure of what you were asking.



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

I apologize but you are almost speaking Greek to me. I actually might even know as much Greek from studying the Bible (which is only a few words) as I know VB Code. lol

I have attempted to learn it before but I have given up. I simply do not have the time to work with it enough for it to stick enough for me to learn. I will use try to make it work for a few days for my job, then I might not need it again for weeks or months.

For that reason I tend to stick with formulas when I am working on Excel. Typically I can find the formulas I dont know on line, but sometimes I need a tough one like this.

Believe me, I wish I could understand VB enough to turn this into using that instead. This file I am using is absolutely filled with formulas comparing up to around a dozen tabs that I populate. But I know formulas half way decently. I cant do VB however hard I have tried in the past.

Thanks though.

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]

The new part of the Bible was originally written in Greek. It is a shame. VBA is much easier than Excel formulas.

 

[Kenja824]

I have spent a lot of time trying to learn VBA. I have a folder full of journals. I just cant seem to put it together to make sense. It is my achilles heal.

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]

Here's a one sheet solution...

The row lookup value is in A1

The column lookup value (in this case God) is in D1

The formula...
E1: =IF(SUM(IFERROR(FIND($D$1,OFFSET($A$5,MATCH($A$1,$A$5:$A$14,0)-1,0,1,COUNTA(5:5))),0))>0,D1,".")

Notice that blue, green and gold return God, the column lookup value in D1.

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

 

[SkipVought]

Here's a multi-sheet solution

Each table on every sheet must have Top Left Cell A5
Column A must only contain values for lookup beginning in A5
Row 5 must have no empty cells if there are values in the column in rows 6 and following.

A1: must be empty
B1: Sheet name
C1: Row lookup value
D1: Column lookup substring
E1: =IF(SUM(IFERROR(FIND($D$1,OFFSET(INDIRECT(B1&"!A5"),MATCH(C1,INDIRECT(B1&"!A:A"),0)-5,0,1,COUNTA(INDIRECT(B1&"!5:5")))),0))>0,D1,".")

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