# Excel Question how to see if entry is always on same line



## evijeens (Jan 4, 2023)

HIya, 
I was Hoping if someone could help with file i am currently preparing. 
I have customer numbers and specific functions and i would need to see if Z3 and Z4 is always on line 5 and 6 for the same customer. 
I have played with Summif and countif but they seem to count only. File is way larger this is just example. I would appreciate any help.


----------



## Peter_SSs (Jan 4, 2023)

Welcome to the MrExcel board!



evijeens said:


> i would need to see if Z3 and Z4 is always on line 5 and 6 for the same customer.


If it *is* on lines 5 & 6 for the customer, what do you want to do?
If it *is not* on lines 5 & 6 for the customer, what do you want to do?

Perhaps you could give us some more sample data but include examples where Z3 & Z4 are not on lines 5 & 6 and also include all the results (manually entered) that you want.
Preferably give us all of that with XL2BB so that we can copy the data for testing.


----------



## evijeens (Jan 4, 2023)

Peter_SSs said:


> Welcome to the MrExcel board!
> 
> 
> If it *is* on lines 5 & 6 for the customer, what do you want to do?
> ...


Thak you  happy to find this community. 

Unfortunately i Can not download anything on my work laptop so i would not be able to upload Minisheet. 
Well Ideally i would want to see IF Z3 And Z4 is on the line 5 and 6 always and if it is on different lone to show FALSE. Basically i am interested if any Partner function is always on same line
SP - 1 
BP - 2 
PY - 3 
SH - 4 

I have done assigned numbers to duplicates so in theory i could filter down, but i do have large file so i was wondering if it would be possible with formulas. 


I have prepared How would look in perfect world.


----------



## Peter_SSs (Jan 4, 2023)

evijeens said:


> Unfortunately i Can not download anything on my work laptop so i would not be able to upload Minisheet.


In future it would be a good idea to clearly state that in your opening post so people like me do not keep asking.
An alternative, not as good as a mini sheet but better than a picture in most cases, is simply to copy/paste from Excel into your post Like this


CustomerPartner Function1​1​1​1​1​Z31​Z42​


I can see why the first 2 rows of customer 4 are False but I'm not sure what the logic is for the next 2 rows of that customer being True. Can you clarify that?


----------



## evijeens (Jan 4, 2023)

Peter_SSs said:


> In future it would be a good idea to clearly state that in your opening post so people like me do not keep asking.
> An alternative, not as good as a mini sheet but better than a picture in most cases, is simply to copy/paste from Excel into your post Like this
> 
> 
> ...


Hiya, Thank  you i will keep that in mind for next posts  

SO if we are looking specifically at customer 4 then first 2 lines has Z3 and Z4 which is incorrect ( they should be on line 5 and 6 ) and they are as well basically in this scenario duplicate entry Line 1 -2 and 5-6 ( which means i have to fix the error - but that is after)  lie 3 and 4 is true because PY and SH should always be on 3 and 4 ( please see example Customer 1 as that is absolutely correct)

CustomerPartner FunctionLine Check4​Z31​FALSE​4​Z42​FALSE​4​PY3​TRUE4​SH4​TRUE4​Z35​TRUE4​Z46​TRUE


Example where is all correct 

CustomerPartner FunctionLine Check1​SP1​TRUE1​BP2​TRUE1​PY3​TRUE1​SH4​TRUE1​Z35​TRUE1​Z46​TRUE4​Z31​FALSE​4​Z42​FALSE​4​PY3​TRUE4​SH4​TRUE4​Z35​TRUE4​Z46​TRUE


----------



## Peter_SSs (Jan 4, 2023)

evijeens said:


> 3 and 4 is true because PY and SH should always be on 3 and 4


Ah, I see. It seems we are checking all 6 lines whereas from post #1 I thought that we were only checking Z3 and Z4. 

Is one of these what you want then? If you already have and want the "Line Check" column then you can use the column D formula. If you only have the "Line Check" column to help with this check we are doing now then you could omit that "Line Check" column and use the column E formula instead. Column E formula does assume that customers are not repeated further down the column though.

23 01 04.xlsmABCDE1CustomerPartner FunctionLine CheckCheck 1Check 221SP1TRUETRUE31BP2TRUETRUE41PY3TRUETRUE51SH4TRUETRUE61Z35TRUETRUE71Z46TRUETRUE84Z31FALSEFALSE94Z42FALSEFALSE104PY3TRUETRUE114SH4TRUETRUE124Z35TRUETRUE134Z46TRUETRUECustomer checkCell FormulasRangeFormulaD2:D13D2=MATCH(B2,{"SP","BP","PY","SH","Z3","Z4"},0)=C2E2:E13E2=MATCH(B2,{"SP","BP","PY","SH","Z3","Z4"},0)=COUNTIF(A$2:A2,A2)


----------



## evijeens (Jan 4, 2023)

Peter_SSs said:


> Ah, I see. It seems we are checking all 6 lines whereas from post #1 I thought that we were only checking Z3 and Z4.
> 
> Is one of these what you want then? If you already have and want the "Line Check" column then you can use the column D formula. If you only have the "Line Check" column to help with this check we are doing now then you could omit that "Line Check" column and use the column E formula instead. Column E formula does assume that customers are not repeated further down the column though.
> 
> 23 01 04.xlsmABCDE1CustomerPartner FunctionLine CheckCheck 1Check 221SP1TRUETRUE31BP2TRUETRUE41PY3TRUETRUE51SH4TRUETRUE61Z35TRUETRUE71Z46TRUETRUE84Z31FALSEFALSE94Z42FALSEFALSE104PY3TRUETRUE114SH4TRUETRUE124Z35TRUETRUE134Z46TRUETRUECustomer checkCell FormulasRangeFormulaD2:D13D2=MATCH(B2,{"SP","BP","PY","SH","Z3","Z4"},0)=C2E2:E13E2=MATCH(B2,{"SP","BP","PY","SH","Z3","Z4"},0)=COUNTIF(A$2:A2,A2)


Thank you  

Yes i am focusing on the Z3 and Z4 but afterwards i came with idea why to focus only on 2 lines if i can check if all match at the same time as once you start to work with the file more questions arise. And when i was initially asking for the question i did not have Lines ( duplicates numbered). As while i was waiting for answers i did look for solutions and well as i did not want to rely only on one source. 

I really appreciate your help thank you


----------



## Peter_SSs (Jan 4, 2023)

You're welcome. Glad to help.


----------

