LinkBack URL
About LinkBacks
Hello
Can someone help me with the following
I want a formula put in that shows if column d has the value p1 in it, add in the sector number
Hello
Can someone help me with the following
I want a formula put in that shows if column d has the value p1 in it, add in the sector number
You can put this formula in E2:
=IF(RIGHT(D2,2)="P1","SECTOR="&A2,"")
then copy down.
Hope this helps.
Pete
You haven't said where you want the formula.
If I was inserting the formula in column E then the formula would be
Formula:
Please Login or Register to view this content.
This formula searches D2 for data that is in B2. if it finds it it brings through the data in cell A2. If it doesn't find it the cells remains blank.
This will allow the formula to be copied down your sheet.
I think Pete's formula will only look for P1 and only look at the last 2 characters in cell D2 (so wouldn't find P10 or P11).
Last edited by Declamatory; 10-28-2016 at 06:20 AM.
Try:Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
@Declamatory,
That picks up P10 and P11.
Pete
My guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanation
Sorry Pete, it does but incorrectly. Cell E3 should have a result of nothing but it has a value of SECTOR=293291 because it is bringing through the Sector for attribute P10 when Cell D3 has an attribute of P1.
The formula doesn't take into account the attributes changing in column B such as P5
Then my formula would beMy guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanationFormula:Please Login or Register to view this content.
@Declamatory@ your formula checks row by row and, effectively, matches the Attribute on that row. It's not looking up the table in columns A:B
Note that the original file has over a million "blank" rows, hence the size of the file. I have deleted them.
Ahh, I see TMS. Good job.
@Declamatory: thanks for the feedback and rep, much appreciated.
All we need now is for the OP to come back and let us know what he's really trying to do
Thanks for this. I cannot seem to get it working
The formula does not appear to be looking at the whole table
Re-post a file showing what is not working.
I need it to pick up the whole table with the correct numbers
You need to change the formula in E2 to this:
=INDEX($A:$A,MATCH(RIGHT($D2,LEN(D2)-(FIND("=",$D2,1))),$B:$B,0))
Use Ctrl-Shift-Enter to commit it, rather than the usual < Enter >, as it is an array formula, and then copy it down as required.
Hope this helps.
Pete
This works but it's NOT an array formula:
Formula:Please Login or Register to view this content.
It looks as though you have changed the text prior to the code.
Last edited by TMS; 11-02-2016 at 08:16 AM.
how do I keep the word sector in it?
Something like:Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks