Alfa Romeo Forum banner

1 - 14 of 14 Posts

·
Administrator
Joined
·
45,537 Posts
Discussion Starter #1
Is this possible?

I want a formula to search column A for a value 'X'. If it finds 'X', then I want it to look across to column F and return a character string from within that field.

Hmm. Thinking aloud (since I'm looking for that strong ti either be "In" or not, I wonder if I could use a countifs instead?

COUNTIFS : column A = X and MID(column F,5,2)="In"

(Obviously that isn't in correct format, but I'm rushing.) Will that work in principle? Can then nest that in an IF which translates it back to an "In" or whatever other text I decide on?
 

·
Registered
Joined
·
3,769 Posts
Is this possible?

I want a formula to search column A for a value 'X'. If it finds 'X', then I want it to look across to column F and return a character string from within that field.

Hmm. Thinking aloud (since I'm looking for that strong ti either be "In" or not, I wonder if I could use a countifs instead?

COUNTIFS : column A = X and MID(column F,5,2)="In"

(Obviously that isn't in correct format, but I'm rushing.) Will that work in principle? Can then nest that in an IF which translates it back to an "In" or whatever other text I decide on?
Could you use nested IF statements to perform the same task?

EDIT: Sorry, I missed the last line. Not sure.
 

·
Registered
Joined
·
3,769 Posts
I forgot how evil Excel is. Can you use a script in Python or such to conduct the same operation?

EDIT: Could you post a minimum working example of a row of data or two?
 

·
Administrator
Joined
·
45,537 Posts
Discussion Starter #5
I'll post something up in the morning when I'm back in work.
 

·
Administrator
Joined
·
45,537 Posts
Discussion Starter #6
See if this works.

I've created a very pared down example since the data is sensitive.

If you look at the listing tab, just the presence of a 'code' there is enough to show that the item was received. You can see the countif nested in an if I've used in colunb B of Summary to check this. (I know - overcomplicated for this purpose, but the same method is used elsewhere where it does need to be so complicated, so standardisation is good.)

In column C I need to pull across whether the item has then been sent out. If the text in listing column B contains 'In' for the relevant Code, then it has not been sent out. If it contains 'Out' then it has.

But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry?

I guess I could do it in two steps? A VLOOKUP to a hidden cell, the a MID from that cell? But is there a way to do it in one?
 

Attachments

·
Registered
Joined
·
3,769 Posts
See if this works.

I've created a very pared down example since the data is sensitive.

If you look at the listing tab, just the presence of a 'code' there is enough to show that the item was received. You can see the countif nested in an if I've used in colunb B of Summary to check this. (I know - overcomplicated for this purpose, but the same method is used elsewhere where it does need to be so complicated, so standardisation is good.)

In column C I need to pull across whether the item has then been sent out. If the text in listing column B contains 'In' for the relevant Code, then it has not been sent out. If it contains 'Out' then it has.

But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry?

I guess I could do it in two steps? A VLOOKUP to a hidden cell, the a MID from that cell? But is there a way to do it in one?
Solving this is not possible within the normal bounds of excel I fear. The problem is, you need to return an array reference where Excel can only return values. This is the problem not being able to create array reference variables on the fly. What you really want is to return an array reference. You will have to use the VLOOKUP function and then create a hidden column in order to solve the problem.

Your problem falls apart where you say "But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry". Excel handles things like

=IF(This, then_do_that, else_do_that)

but there is no way you can recover where 'This' happens and forward it to the 'then_do_that' or 'else_do_that' parts of the formula. It will only tell you if it happened. Same with the VLOOKUP function, and in fact, all of Excel I think. This sort of makes sense as you cannot do that in a scripting language either. What you are really asking is 'how can I make a variable'; you can't sadly, not in the context of a self contained formula:

=IF(This_happens, cell_ref = "Where_this_is_happening", then_do_this_with_cell_ref, or_do_that_with_cell_ref)

We can string search for 'n' in the code column very easily and also parse strings easily. But you can only find how many instances of that code exist, not where they are; more accurately you cannot return them in a single line, hence your problem. You can only create another column which will return an array reference, and then use that particular reference in all of your loops. You could try using VBA, but that means using Visual Basic macro in Excel which is possibly a cause for hanging. I hate Excel sometimes, lovely to look at, but useless for data analysis. :wall:

Excel can do some really cool stuff with graphs, but its inability to do complex searches and return nice variables is annoying. It's well up there with C-Shell scripting in my list of least liked computing activities. :lol:
 

·
Administrator
Joined
·
45,537 Posts
Discussion Starter #8
Cheers David, that was the conclusion I was coming to, but good to get confirmation I wasn't missing something.
 
C

·
Guest
Joined
·
0 Posts
bazza, try putting this into C2 of your first tab and copy down:

=IF(B2="Yes",IF(ISNUMBER(FIND("Out",Listing!B2)),"Yes","No"),"")

It uses the isnumber and find to search for your string (you could put this into a cell if you want) in the corresponding listings worktab.
 

·
Administrator
Joined
·
45,537 Posts
Discussion Starter #10
I like the use of FIND - haven't used that formula before.

I think there's a problem with your suggestion though. It is assuming that the lines on the listing tab are arranged the same as the lines on the summary tab: i.e. when looking for the answer for Summary C2, it's querying only the field Listing B2. In fact, I need it to look at the whole spreadsheet first to identify which line is the correct match (based on column A). That's the bit which I think is not possible.
 

·
Registered
Joined
·
3,769 Posts
I like the use of FIND - haven't used that formula before.

I think there's a problem with your suggestion though. It is assuming that the lines on the listing tab are arranged the same as the lines on the summary tab: i.e. when looking for the answer for Summary C2, it's querying only the field Listing B2. In fact, I need it to look at the whole spreadsheet first to identify which line is the correct match (based on column A). That's the bit which I think is not possible.
Yes, I also tried the above approach (or similar at the time IIRC) with the SEARCH function combined with ISNUMBER, giving a binary result. This also runs into the same problem as you don't have a 'middle' variable that can be set.
 

·
Registered
Joined
·
5,699 Posts
Would you be able to use the MATCH function to find the position of the code (from summary column A) in listing column A, then look in the relative listing column B to find out the result ?

For instance : =MATCH(Summary!A10,Listing!A2:A10,0) will return the value 7, as 9 (the value in Summary!A10)is matched in the 7th row of the array A2:A10
=MATCH(Summary!A11,Listing!A2:A10,0) will return the value 8, as 10(the value in Summary!A11) is matched in the 8th row of the array

and so on...


I would also use a hidden protected tab to do all the formulas on.
 

·
Registered
Joined
·
3,769 Posts
Would you be able to use the MATCH function to find the position of the code (from summary column A) in listing column A, then look in the relative listing column B to find out the result ?

For instance : =MATCH(Summary!A10,Listing!A2:A10,0) will return the value 7, as 9 (the value in Summary!A10)is matched in the 7th row of the array A2:A10
=MATCH(Summary!A11,Listing!A2:A10,0) will return the value 8, as 10(the value in Summary!A11) is matched in the 8th row of the array

and so on...


I would also use a hidden protected tab to do all the formulas on.
I suspect MATCH will also run into the same issue. We are talking about trying to do this in one sweep, and I just see no way of curing the forwarding variable problem without using a VBA.

After MATCH, you will then require an IF statement to forward the result of MATCH to continue the computation. But MATCH will not hold the location of the result implicitly to be passed forward. You can't extract '7' or '8' from the MATCH and then push it through the IF statement as a result. The result is passed to the top level, so the cell becomes the result of MATCH. What you really want is the variable as mentioned above, or a recursive script, niether of which Excel can handle outside of a VBA.

I think another key point is that these data are incomplete. There is no direct one to one correspondance in the different fields. Item 4 in column A of sheet one may not correspond to the same item, more specifically, the same cell in sheet 2. This is where the issue really kicks in, because then you HAVE to know WHERE the item that corresponds to your sent item is and then forward it all in the same line, which is where the problem arises.

By the time you have learned the VBA, you may as well export these data and learn a high level, verbose scripting language like Fortran, which would solve the issue in three lines of code I would think. Stupid Excel... :wall:
 
1 - 14 of 14 Posts
Top