I am trying to compare rows of information. If all cells in a row compare to
al cells in the look-up table, then I want it to return a value in the usual
fashion. An example of the data is

S M T W T F S
7.5 7.5 7.5
Each row is unique, but it must recognise the blanks to remain unique.

Any ideas?

Thanks,

Nick

Not quite sure what you mean by "return a value in the usual fashion". But
I'll give this a couple of shots.

For my example, I just used a short table from A1 to G4 for the weekdays,
and assumed a return value you want is in column H on those rows.

I put a test row at A7:G7 and this formula into H7:
=SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7),$H$1:$H$4)

The 'flaw' in that formula is that it doesn't actually return an individual
value if there are rows in the table at A1:G4 that are duplicates - it will
return the sum of the values in column H for all rows in that table that are
duplicates.

If you just want an indicator of duplicates, then this formula in H7 should
help:
=IF(SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7))<0,"Duplicate","")

That will put the word "Duplicate" into the H# test cell (H7 in my example)
if the values in A7:G7 are also in the table in the same sequence, and leave
the cell blank if not. The formula returns 1 if there's one or more
duplicate rows in A1:A4, and a zero if not. It's basically a True or False
test.

Hope this helps some. And maybe someone will come up with a tighter solution.
"Nick C" wrote:

I am trying to compare rows of information. If all cells in a row compare to
al cells in the look-up table, then I want it to return a value in the usual
fashion. An example of the data is

S M T W T F S
7.5 7.5 7.5
Each row is unique, but it must recognise the blanks to remain unique.

Any ideas?

Thanks,

Nick

Sorry for not explaining myself properly. I will have another go.

sun mon tue wed thu fri sat ref
7.5 7.5 7.5 1
7.5 7.5 7.5 7.5 7.5 2
7.5 7.5 7.5 3
If sunday is blank, monday is 7.5, tuesday is blank, wednesday is 7.5,
thursday is blank, friday is 7.5 and saturday is blank in sheet2 in the
workbook, then this would then match the table above in sheet1, so therefore
insert "1" against the row of mathcing data in sheet2.

Regards,

Nick

"JLatham" wrote:

Not quite sure what you mean by "return a value in the usual fashion". But
I'll give this a couple of shots.

For my example, I just used a short table from A1 to G4 for the weekdays,
and assumed a return value you want is in column H on those rows.

I put a test row at A7:G7 and this formula into H7:
=SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7),$H$1:$H$4)

The 'flaw' in that formula is that it doesn't actually return an individual
value if there are rows in the table at A1:G4 that are duplicates - it will
return the sum of the values in column H for all rows in that table that are
duplicates.

If you just want an indicator of duplicates, then this formula in H7 should
help:
=IF(SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7))<0,"Duplicate","")

That will put the word "Duplicate" into the H# test cell (H7 in my example)
if the values in A7:G7 are also in the table in the same sequence, and leave
the cell blank if not. The formula returns 1 if there's one or more
duplicate rows in A1:A4, and a zero if not. It's basically a True or False
test.

Hope this helps some. And maybe someone will come up with a tighter solution.
"Nick C" wrote:

I am trying to compare rows of information. If all cells in a row compare to
al cells in the look-up table, then I want it to return a value in the usual
fashion. An example of the data is

S M T W T F S
7.5 7.5 7.5
Each row is unique, but it must recognise the blanks to remain unique.

Any ideas?

Thanks,

Nick

Warning, this is ugly.

You will need a helper column, assuming your data on Sheet1 is in columns A
through H, click on row H, and insert a column (this will put your reference
data in column I).
The formula that will go in column H now is as follows (and assuming your
criteria for matching begins in row 2).
=IF(A2="","B",A2)&IF(B2="","B",B2)&IF(C2="","B",C2 )&IF(D2="","B",D2)&IF(E2="","B",E2)&IF(F2="","B",F 2)&IF(G2="","B",G2)

Next, I would define column I as TblData (or some other name that would make
sense for your purpose), and define columns I:H as TblMatch. This will allow
a somewhat easier lookup on Sheet2.

On Sheet2, assuming your data starts in row 2, and assuming your match
criteria is in column H, type the following in H2, and copy down as needed:

=IF(COUNTIF(TblData,(IF(A2="","B",A2)&IF(B2="","B" ,B2)&IF(C2="","B",C2)&IF(D2="","B",D2)&IF(E2="","B ",E2)&IF(F2="","B",F2)&IF(G2="","B",G2)))=0,"N o
Match",VLOOKUP(IF(A2="","B",A2)&IF(B2="","B",B2)&I F(C2="","B",C2)&IF(D2="","B",D2)&IF(E2="","B",E2)& IF(F2="","B",F2)&IF(G2="","B",G2),TblMatch,2,0))

Told you it was ugly, that being said, it does work.



--
John C


"Nick C" wrote:

Sorry for not explaining myself properly. I will have another go.

sun mon tue wed thu fri sat ref
7.5 7.5 7.5 1
7.5 7.5 7.5 7.5 7.5 2
7.5 7.5 7.5 3
If sunday is blank, monday is 7.5, tuesday is blank, wednesday is 7.5,
thursday is blank, friday is 7.5 and saturday is blank in sheet2 in the
workbook, then this would then match the table above in sheet1, so therefore
insert "1" against the row of mathcing data in sheet2.

Regards,

Nick

"JLatham" wrote:

Not quite sure what you mean by "return a value in the usual fashion". But
I'll give this a couple of shots.

For my example, I just used a short table from A1 to G4 for the weekdays,
and assumed a return value you want is in column H on those rows.

I put a test row at A7:G7 and this formula into H7:
=SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7),$H$1:$H$4)

The 'flaw' in that formula is that it doesn't actually return an individual
value if there are rows in the table at A1:G4 that are duplicates - it will
return the sum of the values in column H for all rows in that table that are
duplicates.

If you just want an indicator of duplicates, then this formula in H7 should
help:
=IF(SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7))<0,"Duplicate","")

That will put the word "Duplicate" into the H# test cell (H7 in my example)
if the values in A7:G7 are also in the table in the same sequence, and leave
the cell blank if not. The formula returns 1 if there's one or more
duplicate rows in A1:A4, and a zero if not. It's basically a True or False
test.

Hope this helps some. And maybe someone will come up with a tighter solution.
"Nick C" wrote:

I am trying to compare rows of information. If all cells in a row compare to
al cells in the look-up table, then I want it to return a value in the usual
fashion. An example of the data is

S M T W T F S
7.5 7.5 7.5
Each row is unique, but it must recognise the blanks to remain unique.

Any ideas?

Thanks,

Nick

Many thanks John. I will give it a go.

Regards,

Nick

"John C" wrote:

Warning, this is ugly.

You will need a helper column, assuming your data on Sheet1 is in columns A
through H, click on row H, and insert a column (this will put your reference
data in column I).
The formula that will go in column H now is as follows (and assuming your
criteria for matching begins in row 2).
=IF(A2="","B",A2)&IF(B2="","B",B2)&IF(C2="","B",C2 )&IF(D2="","B",D2)&IF(E2="","B",E2)&IF(F2="","B",F 2)&IF(G2="","B",G2)

Next, I would define column I as TblData (or some other name that would make
sense for your purpose), and define columns I:H as TblMatch. This will allow
a somewhat easier lookup on Sheet2.

On Sheet2, assuming your data starts in row 2, and assuming your match
criteria is in column H, type the following in H2, and copy down as needed:

=IF(COUNTIF(TblData,(IF(A2="","B",A2)&IF(B2="","B" ,B2)&IF(C2="","B",C2)&IF(D2="","B",D2)&IF(E2="","B ",E2)&IF(F2="","B",F2)&IF(G2="","B",G2)))=0,"N o
Match",VLOOKUP(IF(A2="","B",A2)&IF(B2="","B",B2)&I F(C2="","B",C2)&IF(D2="","B",D2)&IF(E2="","B",E2)& IF(F2="","B",F2)&IF(G2="","B",G2),TblMatch,2,0))

Told you it was ugly, that being said, it does work.



--
John C


"Nick C" wrote:

Sorry for not explaining myself properly. I will have another go.

sun mon tue wed thu fri sat ref
7.5 7.5 7.5 1
7.5 7.5 7.5 7.5 7.5 2
7.5 7.5 7.5 3
If sunday is blank, monday is 7.5, tuesday is blank, wednesday is 7.5,
thursday is blank, friday is 7.5 and saturday is blank in sheet2 in the
workbook, then this would then match the table above in sheet1, so therefore
insert "1" against the row of mathcing data in sheet2.

Regards,

Nick

"JLatham" wrote:

Not quite sure what you mean by "return a value in the usual fashion". But
I'll give this a couple of shots.

For my example, I just used a short table from A1 to G4 for the weekdays,
and assumed a return value you want is in column H on those rows.

I put a test row at A7:G7 and this formula into H7:
=SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7),$H$1:$H$4)

The 'flaw' in that formula is that it doesn't actually return an individual
value if there are rows in the table at A1:G4 that are duplicates - it will
return the sum of the values in column H for all rows in that table that are
duplicates.

If you just want an indicator of duplicates, then this formula in H7 should
help:
=IF(SUMPRODUCT(--($A$1:$A$4=A7),--($B$1:$B$4=B7),--($C$1:$C$4=C7),--($D$1:$D$4=D7),--($E$1:$E$4=E7),--($F$1:$F$4=F7),--($G$1:$G$4=G7))<0,"Duplicate","")

That will put the word "Duplicate" into the H# test cell (H7 in my example)
if the values in A7:G7 are also in the table in the same sequence, and leave
the cell blank if not. The formula returns 1 if there's one or more
duplicate rows in A1:A4, and a zero if not. It's basically a True or False
test.

Hope this helps some. And maybe someone will come up with a tighter solution.
"Nick C" wrote:

I am trying to compare rows of information. If all cells in a row compare to
al cells in the look-up table, then I want it to return a value in the usual
fashion. An example of the data is

S M T W T F S
7.5 7.5 7.5
Each row is unique, but it must recognise the blanks to remain unique.

Any ideas?

Thanks,

Nick