Data Sorting

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
Hello,

Here is my question I have not been using excel for a few years but I am back and need to have a memory refresh

here is my delema

I have a excel spread sheet with 2 pages and on the first is where i want the data to populated, sheet1 has 2 columns. The first is blank and the second list a username the first is where i want the data i need entered

on the second sheet is a imported txt file broken down into columns and i am only concerned with the first 2

here is how i want it to work

sheet 1

column 1 column 2
??? username
??? username1

Sheet 2

column 1 column 2
testing username
testing username1
under username2

I want the code to look into sheet 2 in column 2 to find the username but if column 1 does not have the word testing then i do not want it to show
and if the word testing is there I will then want the code to display a "Yes"
In column 1 on sheet 1

any help would be great

Thank you
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
ok thank you

This was alot of help

to continue on what i had
how can you then add if there is a word called "down" in the cell below the key word in column 2

sheet 1

column 1 column 2
??? username
??? username1

Sheet 2

column 1 column 2
testing username
testing username1
down
under username2

this is the result that i would like to get on Sheet 1

Column 1 column 2
yes username
username1
 
Upvote 0
Now I think you lost me.

Can you use the HTML Maker to post a sample of the sheet and expected results?
 
Upvote 0
I am sorry here should be a better example



Sheet 1

Column 1 Column 2
????? user1
????? user2
????? user3



sheet 2

Column 1 Column 2
testing user1
down

testing user2

test user3

i would like the code to search sheet 2 for Column 2 and if it is found with the word testing in column 1 on sheet 2 to display "yes" in column1 of sheet 1

But if it finds the word "testing" in column 1 i then want the code to look at the cell directly below it so if testing is in A1 i want the code to look at A2 for the word "down" and if it is there i do not want the word "yes" displayed on Sheet 1 Column 1
 
Upvote 0
well, not sure I understand where you're going.. but I think this does what you describe...

Code:
=IF(AND(INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))="testing",INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)+1)<>"down"),"Yes","")
 
Upvote 0
ok this is what I want to do,
the below is correct all but one thing as you can see the is cell H:H contans the match data but what i want to do is that if it finds the word testing and it finds the matching username I will then want it to look at the cell directly below to make sure the word down is not there but if it is i do not want it to display a yes
Book1
ABCDEFGHI
1yesusernameMinimum
2yesusername1testingUsername
3FALSEusername2down
4testingusername1
5testusername2
6
7
Sheet1
 
Upvote 0
OK, so the word "down" will appear below the USERNAME in column H, not TESTING Column G?? Right?

Code:
=IF(AND(INDEX(G:G,MATCH(B1,H:H,0))="testing",INDEX(H:H,MATCH(B1,H:H,0)+1)<>"down"),"yes","")
personal.xls
ABCDEFGHI
1 usernameMinimum
2yesusername1testingUsername
3 username2down
4testingusername1
5testusername2
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top