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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Code:
=IF(INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))="testing","Yes","")
 

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Now I think you lost me.

Can you use the HTML Maker to post a sample of the sheet and expected results?
 

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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","")
 

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
sorry it does not make the value in Sheet 1 column 1 change from the yes
 

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Forum statistics

Threads
1,181,055
Messages
5,927,863
Members
436,573
Latest member
CMR237

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
Top