Search a column and code all rows with matching value in another column

[lazypony]

Hi,

I have eye tracking data with 1 row per eye movement with multiple eye movements in each trial. I need to exclude some trials with eye movements that do not meet a set criteria.

I have a column of 0 and 1 for eye movements that fail to meet the criteria. I now need to code 1 for all the rows that come from those same trials in order to exclude the trials.

It sounds straightforward but I have tried lookup and match functions but am not proficient enough in excel to work it out.

I have attached a small example file here.

Any help would be very much appreciated.

Thanks!

[L-Drr]

In your file in cell I2 you mention column E but I think you mean column G. Do you also mean H where you say F?

[lazypony]

AH! Sorry, yes I do. I modified the file and not the text - sincere apologies.

[L-Drr]

No worries.
Let me try to rephrase your problem: for all the rows with a "1" for Invalid Start you want to see which UID numbers are the same? Am I right?

[L-Drr]

Or do you want all duplicate UID numbers to be marked so that you only keep the unique ones?

[lazypony]

Hi,

Yes you are right in the first instance, I need a '1' to signal all the UID numbers that are the same as the UID found on the row where there is a 1 in the Invalid Start column. Then I can select all the 1's and dump all the trials that have an invalid start eye movement.

Hope this makes sense!

Thanks so much.

[L-Drr]

Ahh wait let my try one more time:
If there are five records with UID=12345, and one of them has a 1 for Invalid Start, then you want the other four to be marked with a 1 so you can remove them?
And if several have a 1 for Invalid Start, you want to keep all of those and remove the rest?
And if there are records with UID=67890 and none have a 1 for Invalid Start, you want to keep them all? Or remove them all?

BTW I guess "1" means NO invalid start?

[lazypony]

Hi,

Yes sorry it all seems a rather illogical way of going about things:

Yes I would want the remaining four records to be marked with a 1 so then I can remove them. If there are records where none have a 1 for invalid start I want to keep them.

1 does mean it was an invalid start

Thanks again!

[L-Drr]

Ok I've found a solution but you'll have to add one extra column (which you can hide if you like):

Fill column I with this, starting at I2: =G2&H2 (then click-drag downwards)
Fill column J with this, starting at J2: =IF(AND(COUNTIF(I:I;G2&1)=1;VALUE(H2)=0);0;1) (and again click-drag downwards)

Then column J should have 1's for the records that you want to keep. Does that do the trick?

Edit: or those are the ones you want to remove, I'm still confused

[lazypony]

Hi - fantastic to help me!!

I am pasting it in and excel is looking for something in the formula to correct with the I:I in the bracket after count if - is it meant to be ...(I:I2;...?

=IF(AND(COUNTIF(I:I;G2&1)=1;VALUE(H2)=0);0;1)

[L-Drr]

I:I means the whole column I. You can also use I$2:I$1000 (1000 being the end of your records, you can also pick another number)

Edit: or maybe you should get rid of the Mac :P

[lazypony]

Hey,

I am so pleased you are letting me take up your saturday afternoon - I have pasted it in and it tells me there is an error - I have attached the file here again - sorry about this - I'm hoping you like problem solving and puzzles...

Thanks,

Jude.

[L-Drr]

Yeah I like this, that's why I'm on this forum

I see what the problem is: in your version the numbers are not recognised as such. In my version they are so I can only guess that the following should work for you:
Replace G2&1 with VALUE(G2&1)
Do not remove any of the other characters. If that doesn't work either, try replacing G2&1 with VALUE(G2)&1 or VALUE(VALUE(G2)&1)

[lazypony]

Hi,

Ok so I have tried these three different functions and the error remains - are these the options you suggested or am I doing something wrong?

IF(AND(COUNTIF(I:I;VALUE(G2&1))=1;VALUE(H2)=0);0;1)

IF(AND(COUNTIF(I:I;VALUE(G2)&1)=1;VALUE(H2)=0);0;1)

IF(AND(COUNTIF(I:I;VALUE(VALUE(G2)&1))=1;VALUE(H2)=0);0;1)

So sorry about this...

[L-Drr]

Hmm yeah that should be it. How about this: go back to using the old formula for column J and change the formulas in columns G, H and I. Put the whole formulas (except for the = at the beginning) between VALUE( and )

[lazypony]

Sorry I am confused here are the formulas in each of the columns, what would you like me to put in each column?

column g: =C2&D2

column h: =IF(OR(A2=".",A2=5,A2=6)*AND(B2=1),"1","0")

column i: =G2&H2

column J: =IF(AND(COUNTIF(I:I;G2&1)=1;VALUE(H2)=0);0;1)

Thanks again!

[L-Drr]

G becomes =VALUE(C2&D2)
H becomes =VALUE(IF(OR(A2=".",A2=5,A2=6)*AND(B2=1),"1","0"))
I becomes =VALUE(G2&H2)
J stays like that

[lazypony]

Hi,

Sorry about this - have done that and excel still says there is an error in column J

=IF(AND(COUNTIF(I:I;G2&1)=1;VALUE(H2)=0);0;1)

Apologies this is such a nightmare...

[L-Drr]

OK then let's split it up to find the problem

K2: =G2&1
L2: =COUNTIF(I:I;K2)
M2: =IF(L2=1;"yes";"no")
N2: =IF(H2=0;"yes";"no")
O2: =IF(M2&N2="yesyes";"keep";"throw away")

You only have to fill this 2nd row

Which ones give errors?

[lazypony]

Hi,

Ok I've done that:
column K is ok
column L says it has an error and highlights the 2nd 'I' in the formula
Column M involves column L so not calculation
Column N says there is an error and highlights the 0
Column O involves the others so no calculation

This is so weird but thanks for soldiering on. I am at a loss as why it says there are these errors

[L-Drr]

Ok change L2 to this: =COUNTIF(I$2:I$500;K2)
And change N2 to this: =IF(VALUE(H2)=0;"yes";"no")

[oeldere]

@lazypony

post your last edited excel file, so we can take a look at it.

[L-Drr]

Haha I just got a private message from another forum user. He/she says to replace the ";" with "," since there is a syntax difference between The Netherlands and the UK

[lazypony]

Hi,

So pleased you guys are such determined problem solvers! I've attached the latest file here

[L-Drr]

Dould you put the ='s back before the formula's before uploading? I use the Dutch version of Excel and now I have to manually translate all the countif's etc

[oeldere]

You have to start each formula with an =

See the attached file.

[lazypony]

Hi,

So pleased you are happy to help, I've opened the file you sent and I've copied down and the outputs for the 'keep / throw away' doesn't match up with what the criteria I'd like to use. I attached it here with the decisions I need in column J.

Sorry if it is just a case of a little tweaking that I could do but I was be really grateful if you'd take another look.

Thanks.

[oeldere]

Please reply the criteria for that range (throw away, keep); which you put manualy in the sheet.

[L-Drr]

But the errors are gone?

[lazypony]

Yes - sorry they are gone!! Apologies yes that's resolved - phew!

[L-Drr]

So the criterium is: if there is any record with a 1 for invalid start, then throw away all records with that UID number

[L-Drr]

That makes everything much easier: you don't need columns K through O anymore (but keep them for now), just type in column P:
=IF(COUNTIF(I:I;G2&1)>0;"throw away";"keep")

remember the ,'s instead of ;'s

[oeldere]

@L-Drr

if you upload your file, the formula will automatic change if the OP opens the file.

I also work with the dutch version, so I like OP to post there file, so I could upload it without translating the formula.

In that case you won't have to worry about the ; (semicolon) or , (colon).

[L-Drr]

True that
See attachment

[lazypony]

whoopie!!! It works!! Thank you so much L-Drr and oeldere!! You are generous geniuses and I am eternally grateful - continue onward with your excel super hero quests!!!

Have a fantastic weekend.

[L-Drr]

Or what's left of it :P
Just kidding, you're welcome.

Please add reputation and mark thread as Solved

[oeldere]

@lazypony

thanks for the reply.

glad i could help.

you can add rep(utationpoints) to the one who helped you by clicking on the star.

[lazypony]

I know - what a marathon!!

Ok - yes will do that with the rep points and stars and as many as possible!