Find row in table which contains specific data

W

wbtczn

New Member
Joined
Aug 31, 2011
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
I don't know if I'm just too tired or what, but my brain isn't working this out. I thought it was an INDEX MATCH situation, but now I don't know. Help?

What I need to have happen is to determine which week a specific matchup occurs. Assuming the first column is week and the next two column are matchups (in reality, there will be five columns of matchups) how would I get Excel to tell me that D3M1 is week 6?

1 D1D2 D3D4
2 M1D1 D2D3
3 D1D3 M2D5
4 M2D1 D5D3
5 D1D4 D3D2
6 M3D1 M4D3
7 D1D5 D3M1
8 M4D1 M5D3
9 D1M1 D3M2
10 M5D1 M1D3
11 D1M2 D3M3
12 D2D1 D4D3
13 D1M3 D3M4
 
Sort by date Sort by votes
...so if I do a COUNTIF across the cells where I have the combined "D1M2" and conditionally format anything greater than 1 I then see that I have duplicated a combination. It doesn't tell me specifically where, but I can easily find the occurrences of the duplicated value.


Excel 2012
AAABACADAE
1G1G2G3G4G5
2D1D2D3D4D5M5M3M4
3M4D1D2D3D4D5M5M3M2M1
4D1D3M2D5   
5M2D1D5D3   
6D1D4D3D2   
7M3D1M4D3   
8D1D5D3M1   
9M4D1M5D3   
10D1M1D3M2   
11M5D1M1D3   
12D1M2D3M3   
13D2D1D4D3   
14D1M3D3M4   
15D3D1    
16D1M4D3M5   
17D4D1M3D3   
18D1M5    
19D5D1    
Schedule
Cell Formulas
RangeFormula
AE2=CONCATENATE(K2,L2)
AE3=CONCATENATE(K3,L3)
AE4=CONCATENATE(K4,L4)
AE5=CONCATENATE(K5,L5)
AE6=CONCATENATE(K6,L6)
AE7=CONCATENATE(K7,L7)
AE8=CONCATENATE(K8,L8)
AE9=CONCATENATE(K9,L9)
AE10=CONCATENATE(K10,L10)
AE11=CONCATENATE(K11,L11)
AE12=CONCATENATE(K12,L12)
AE13=CONCATENATE(K13,L13)
AE14=CONCATENATE(K14,L14)
AE15=CONCATENATE(K15,L15)
AE16=CONCATENATE(K16,L16)
AE17=CONCATENATE(K17,L17)
AE18=CONCATENATE(K18,L18)
AE19=CONCATENATE(K19,L19)
AA2=CONCATENATE(C2,D2)
AA3=CONCATENATE(C3,D3)
AA4=CONCATENATE(C4,D4)
AA5=CONCATENATE(C5,D5)
AA6=CONCATENATE(C6,D6)
AA7=CONCATENATE(C7,D7)
AA8=CONCATENATE(C8,D8)
AA9=CONCATENATE(C9,D9)
AA10=CONCATENATE(C10,D10)
AA11=CONCATENATE(C11,D11)
AA12=CONCATENATE(C12,D12)
AA13=CONCATENATE(C13,D13)
AA14=CONCATENATE(C14,D14)
AA15=CONCATENATE(C15,D15)
AA16=CONCATENATE(C16,D16)
AA17=CONCATENATE(C17,D17)
AA18=CONCATENATE(C18,D18)
AA19=CONCATENATE(C19,D19)
AB2=CONCATENATE(E2,F2)
AB3=CONCATENATE(E3,F3)
AB4=CONCATENATE(E4,F4)
AB5=CONCATENATE(E5,F5)
AB6=CONCATENATE(E6,F6)
AB7=CONCATENATE(E7,F7)
AB8=CONCATENATE(E8,F8)
AB9=CONCATENATE(E9,F9)
AB10=CONCATENATE(E10,F10)
AB11=CONCATENATE(E11,F11)
AB12=CONCATENATE(E12,F12)
AB13=CONCATENATE(E13,F13)
AB14=CONCATENATE(E14,F14)
AB15=CONCATENATE(E15,F15)
AB16=CONCATENATE(E16,F16)
AB17=CONCATENATE(E17,F17)
AB18=CONCATENATE(E18,F18)
AB19=CONCATENATE(E19,F19)
AC2=CONCATENATE(G2,H2)
AC3=CONCATENATE(G3,H3)
AC4=CONCATENATE(G4,H4)
AC5=CONCATENATE(G5,H5)
AC6=CONCATENATE(G6,H6)
AC7=CONCATENATE(G7,H7)
AC8=CONCATENATE(G8,H8)
AC9=CONCATENATE(G9,H9)
AC10=CONCATENATE(G10,H10)
AC11=CONCATENATE(G11,H11)
AC12=CONCATENATE(G12,H12)
AC13=CONCATENATE(G13,H13)
AC14=CONCATENATE(G14,H14)
AC15=CONCATENATE(G15,H15)
AC16=CONCATENATE(G16,H16)
AC17=CONCATENATE(G17,H17)
AC18=CONCATENATE(G18,H18)
AC19=CONCATENATE(G19,H19)
AD3=CONCATENATE(I3,J3)
AD4=CONCATENATE(I4,J4)
AD5=CONCATENATE(I5,J5)
AD6=CONCATENATE(I6,J6)
AD7=CONCATENATE(I7,J7)
AD8=CONCATENATE(I8,J8)
AD9=CONCATENATE(I9,J9)
AD10=CONCATENATE(I10,J10)
AD11=CONCATENATE(I11,J11)
AD12=CONCATENATE(I12,J12)
AD13=CONCATENATE(I13,J13)
AD14=CONCATENATE(I14,J14)
AD15=CONCATENATE(I15,J15)
AD16=CONCATENATE(I16,J16)
AD17=CONCATENATE(I17,J17)
AD18=CONCATENATE(I18,J18)
AD19=CONCATENATE(I19,J19)



Excel 2012
ABCDEFGHIJKL
25Home
26D1D2D3D4D5M1M2M3M4M5
27VisitorD10111111111
28D21010000000
29D31101011111
30D41010100000
31D51010000001
32M10010000000
33M21000110000
34M31010000010
35M42010000000
36M51010000100
Schedule
Cell Formulas
RangeFormula
C27=COUNTIF(Combined_Matchups,CONCATENATE($B27,C$26))
C28=COUNTIF(Combined_Matchups,CONCATENATE($B28,C$26))
C29=COUNTIF(Combined_Matchups,CONCATENATE($B29,C$26))
C30=COUNTIF(Combined_Matchups,CONCATENATE($B30,C$26))
C31=COUNTIF(Combined_Matchups,CONCATENATE($B31,C$26))
C32=COUNTIF(Combined_Matchups,CONCATENATE($B32,C$26))
C33=COUNTIF(Combined_Matchups,CONCATENATE($B33,C$26))
C34=COUNTIF(Combined_Matchups,CONCATENATE($B34,C$26))
C35=COUNTIF(Combined_Matchups,CONCATENATE($B35,C$26))
C36=COUNTIF(Combined_Matchups,CONCATENATE($B36,C$26))
D27=COUNTIF(Combined_Matchups,CONCATENATE($B27,D$26))
D28=COUNTIF(Combined_Matchups,CONCATENATE($B28,D$26))
D29=COUNTIF(Combined_Matchups,CONCATENATE($B29,D$26))
D30=COUNTIF(Combined_Matchups,CONCATENATE($B30,D$26))
D31=COUNTIF(Combined_Matchups,CONCATENATE($B31,D$26))
D32=COUNTIF(Combined_Matchups,CONCATENATE($B32,D$26))
D33=COUNTIF(Combined_Matchups,CONCATENATE($B33,D$26))
D34=COUNTIF(Combined_Matchups,CONCATENATE($B34,D$26))
D35=COUNTIF(Combined_Matchups,CONCATENATE($B35,D$26))
D36=COUNTIF(Combined_Matchups,CONCATENATE($B36,D$26))
E27=COUNTIF(Combined_Matchups,CONCATENATE($B27,E$26))
E28=COUNTIF(Combined_Matchups,CONCATENATE($B28,E$26))
E29=COUNTIF(Combined_Matchups,CONCATENATE($B29,E$26))
E30=COUNTIF(Combined_Matchups,CONCATENATE($B30,E$26))
E31=COUNTIF(Combined_Matchups,CONCATENATE($B31,E$26))
E32=COUNTIF(Combined_Matchups,CONCATENATE($B32,E$26))
E33=COUNTIF(Combined_Matchups,CONCATENATE($B33,E$26))
E34=COUNTIF(Combined_Matchups,CONCATENATE($B34,E$26))
E35=COUNTIF(Combined_Matchups,CONCATENATE($B35,E$26))
E36=COUNTIF(Combined_Matchups,CONCATENATE($B36,E$26))
F27=COUNTIF(Combined_Matchups,CONCATENATE($B27,F$26))
F28=COUNTIF(Combined_Matchups,CONCATENATE($B28,F$26))
F29=COUNTIF(Combined_Matchups,CONCATENATE($B29,F$26))
F30=COUNTIF(Combined_Matchups,CONCATENATE($B30,F$26))
F31=COUNTIF(Combined_Matchups,CONCATENATE($B31,F$26))
F32=COUNTIF(Combined_Matchups,CONCATENATE($B32,F$26))
F33=COUNTIF(Combined_Matchups,CONCATENATE($B33,F$26))
F34=COUNTIF(Combined_Matchups,CONCATENATE($B34,F$26))
F35=COUNTIF(Combined_Matchups,CONCATENATE($B35,F$26))
F36=COUNTIF(Combined_Matchups,CONCATENATE($B36,F$26))
G27=COUNTIF(Combined_Matchups,CONCATENATE($B27,G$26))
G28=COUNTIF(Combined_Matchups,CONCATENATE($B28,G$26))
G29=COUNTIF(Combined_Matchups,CONCATENATE($B29,G$26))
G30=COUNTIF(Combined_Matchups,CONCATENATE($B30,G$26))
G31=COUNTIF(Combined_Matchups,CONCATENATE($B31,G$26))
G32=COUNTIF(Combined_Matchups,CONCATENATE($B32,G$26))
G33=COUNTIF(Combined_Matchups,CONCATENATE($B33,G$26))
G34=COUNTIF(Combined_Matchups,CONCATENATE($B34,G$26))
G35=COUNTIF(Combined_Matchups,CONCATENATE($B35,G$26))
G36=COUNTIF(Combined_Matchups,CONCATENATE($B36,G$26))
H27=COUNTIF(Combined_Matchups,CONCATENATE($B27,H$26))
H28=COUNTIF(Combined_Matchups,CONCATENATE($B28,H$26))
H29=COUNTIF(Combined_Matchups,CONCATENATE($B29,H$26))
H30=COUNTIF(Combined_Matchups,CONCATENATE($B30,H$26))
H31=COUNTIF(Combined_Matchups,CONCATENATE($B31,H$26))
H32=COUNTIF(Combined_Matchups,CONCATENATE($B32,H$26))
H33=COUNTIF(Combined_Matchups,CONCATENATE($B33,H$26))
H34=COUNTIF(Combined_Matchups,CONCATENATE($B34,H$26))
H35=COUNTIF(Combined_Matchups,CONCATENATE($B35,H$26))
H36=COUNTIF(Combined_Matchups,CONCATENATE($B36,H$26))
I27=COUNTIF(Combined_Matchups,CONCATENATE($B27,I$26))
I28=COUNTIF(Combined_Matchups,CONCATENATE($B28,I$26))
I29=COUNTIF(Combined_Matchups,CONCATENATE($B29,I$26))
I30=COUNTIF(Combined_Matchups,CONCATENATE($B30,I$26))
I31=COUNTIF(Combined_Matchups,CONCATENATE($B31,I$26))
I32=COUNTIF(Combined_Matchups,CONCATENATE($B32,I$26))
I33=COUNTIF(Combined_Matchups,CONCATENATE($B33,I$26))
I34=COUNTIF(Combined_Matchups,CONCATENATE($B34,I$26))
I35=COUNTIF(Combined_Matchups,CONCATENATE($B35,I$26))
I36=COUNTIF(Combined_Matchups,CONCATENATE($B36,I$26))
J27=COUNTIF(Combined_Matchups,CONCATENATE($B27,J$26))
J28=COUNTIF(Combined_Matchups,CONCATENATE($B28,J$26))
J29=COUNTIF(Combined_Matchups,CONCATENATE($B29,J$26))
J30=COUNTIF(Combined_Matchups,CONCATENATE($B30,J$26))
J31=COUNTIF(Combined_Matchups,CONCATENATE($B31,J$26))
J32=COUNTIF(Combined_Matchups,CONCATENATE($B32,J$26))
J33=COUNTIF(Combined_Matchups,CONCATENATE($B33,J$26))
J34=COUNTIF(Combined_Matchups,CONCATENATE($B34,J$26))
J35=COUNTIF(Combined_Matchups,CONCATENATE($B35,J$26))
J36=COUNTIF(Combined_Matchups,CONCATENATE($B36,J$26))
K27=COUNTIF(Combined_Matchups,CONCATENATE($B27,K$26))
K28=COUNTIF(Combined_Matchups,CONCATENATE($B28,K$26))
K29=COUNTIF(Combined_Matchups,CONCATENATE($B29,K$26))
K30=COUNTIF(Combined_Matchups,CONCATENATE($B30,K$26))
K31=COUNTIF(Combined_Matchups,CONCATENATE($B31,K$26))
K32=COUNTIF(Combined_Matchups,CONCATENATE($B32,K$26))
K33=COUNTIF(Combined_Matchups,CONCATENATE($B33,K$26))
K34=COUNTIF(Combined_Matchups,CONCATENATE($B34,K$26))
K35=COUNTIF(Combined_Matchups,CONCATENATE($B35,K$26))
K36=COUNTIF(Combined_Matchups,CONCATENATE($B36,K$26))
L27=COUNTIF(Combined_Matchups,CONCATENATE($B27,L$26))
L28=COUNTIF(Combined_Matchups,CONCATENATE($B28,L$26))
L29=COUNTIF(Combined_Matchups,CONCATENATE($B29,L$26))
L30=COUNTIF(Combined_Matchups,CONCATENATE($B30,L$26))
L31=COUNTIF(Combined_Matchups,CONCATENATE($B31,L$26))
L32=COUNTIF(Combined_Matchups,CONCATENATE($B32,L$26))
L33=COUNTIF(Combined_Matchups,CONCATENATE($B33,L$26))
L34=COUNTIF(Combined_Matchups,CONCATENATE($B34,L$26))
L35=COUNTIF(Combined_Matchups,CONCATENATE($B35,L$26))
L36=COUNTIF(Combined_Matchups,CONCATENATE($B36,L$26))
Named Ranges
NameRefers ToCells
Combined_Matchups=Schedule!$AA$2:$AE$21
 
Last edited:
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
wbtczn said:
...so if I do a COUNTIF across the cells where I have the combined "D1M2" and conditionally format anything greater than 1 I then see that I have duplicated a combination. It doesn't tell me specifically where, but I can easily find the occurrences of the duplicated value.
Click to expand...
This and your COUNTIF has solved your issue now?



Two things re the HTML Maker:
1. It was written before Excel 2013 existed so it just happens that the code for the HTML Maker returns 2012 when you are actually using 2013. :)

2. In the HTML Maker menu, please investigate "Generate Html (specify option)" and "Set Default Options" & test them in the Test Here forum.
There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,019
Messages
6,122,707
Members
449,093
Latest member
Mnur

Share this page

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