INDEX/MATCH Function Question

C

cegbuna

New Member
Joined
Mar 26, 2011
Messages
37
I have 2 worksheets and am able to use INDEX/MATCH function to look up data from sheet 2. Some data have multiple rows and when I run the lookup it only returns the data in the first row. For example when I look up Name 1, it returns only the data in row 1. Is there a way to run this look up function so that it returns all 3 rows associated with Name 1 for example?

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Name</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>School</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Game</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Location</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name 1</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name 1</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name 1</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name 2</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name 2</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Data</TD></TR></TBODY></TABLE>

Thanks for the help.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
It looks like you are trying to find all the records that match "name 1". Why wouldn't you use filter or advanced filter?
 
Upvote 0
cegbuna said:
I have 2 worksheets and am able to use INDEX/MATCH function to look up data from sheet 2. Some data have multiple rows and when I run the lookup it only returns the data in the first row. For example when I look up Name 1, it returns only the data in row 1. Is there a way to run this look up function so that it returns all 3 rows associated with Name 1 for example?

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>School</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Game</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>Location</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Name 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Name 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Name 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Data</TD></TR></TBODY></TABLE>

Thanks for the help.
Click to expand...
Sheet2, A1:D6...

<TABLE style="WIDTH: 181pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=243><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2104" width=59><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width=54><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2332" width=66><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=59>School</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=54>Game</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=66>Location</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59 u1:num="4500073803">School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54 u1:num>Game-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59 u1:num="4500074179">School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54 u1:num>Game-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59 u1:num="4500074509">School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54 u1:num>Game-2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59 u1:num="4500073803">School-2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54 u1:num>Game-2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59 u1:num="4500074509">School-2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54 u1:num>Game-3</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-B</TD></TR></TBODY></TABLE>

Sheet1, A1:D8...

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num>name 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64 u1:num u1:fmla="=COUNTIF($A$2:$A$9,A13)">3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=64>Idx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>School</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Game</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Location</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 width=64 u1:num u1:fmla='=IF(ROWS($A$16:A16)<=$A$14,SMALL(IF($A$2:$A$9=$A$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$16:A16)),"")' u1:arrayrange="A16">1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:num="4500073803" u1:fmla='=IF(N($A16),INDEX($B$2:$F$9,$A16,MATCH(B$15,$B$1:$F$1,0)),"")'>School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:num u1:fmla='=IF(N($A16),INDEX($B$2:$F$9,$A16,MATCH(C$15,$B$1:$F$1,0)),"")'>Game-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:fmla='=IF(N($A16),INDEX($B$2:$F$9,$A16,MATCH(D$15,$B$1:$F$1,0)),"")'>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 width=64 u1:num u1:fmla='=IF(ROWS($A$16:A17)<=$A$14,SMALL(IF($A$2:$A$9=$A$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$16:A17)),"")' u1:arrayrange="A17">2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:num="4500074509" u1:fmla='=IF(N($A17),INDEX($B$2:$F$9,$A17,MATCH(B$15,$B$1:$F$1,0)),"")'>School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:num u1:fmla='=IF(N($A17),INDEX($B$2:$F$9,$A17,MATCH(C$15,$B$1:$F$1,0)),"")'>Game-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64 u1:fmla='=IF(N($A17),INDEX($B$2:$F$9,$A17,MATCH(D$15,$B$1:$F$1,0)),"")'>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 width=64 u1:num u1:fmla='=IF(ROWS($A$16:A17)<=$A$14,SMALL(IF($A$2:$A$9=$A$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$16:A17)),"")' u1:arrayrange="A17">3</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64>School-1</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64>Game-2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64>Loc-A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 width=64 u1:num u1:fmla='=IF(ROWS($A$16:A17)<=$A$14,SMALL(IF($A$2:$A$9=$A$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$16:A17)),"")' u1:arrayrange="A17"></TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64></TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64></TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=64></TD></TR></TBODY></TABLE>

A2: name 1

A3, just enter:
Rich (BB code): 
=COUNTIF(Sheet2!$A$2:$A$6,A2)

A5, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(ROWS($A$5:A5)<=$A$3,
   SMALL(
     IF(Sheet2!$A$2:$A$6=$A$2,
        ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),
     ROWS($A$5:A5)),
   "")

B5, just enter, copy across, and down:
Rich (BB code): 
=IF(N($A5),
    INDEX(Sheet2!$B$2:$D$6,$A5,MATCH(B$4,Sheet2!$B$1:$D$1,0)),
    "")
 
Upvote 0
Just a follow up to your solution. Can this be done in Access as well? I am trying to create a clean database but am using Excel to clean up the data before exporting into access.

Thanks.
 
Upvote 0
cegbuna said:
Just a follow up to your solution. Can this be done in Access as well? I am trying to create a clean database but am using Excel to clean up the data before exporting into access.

Thanks.
Click to expand...

You need to devise a key that would make each record distinct/unique. Once you have that, a where query is all what you need to make a selection.
 
Upvote 0
I successfully created a search form for my data in access using a unique identifier. I am experiencing the same problem I did in excel before you helped me out (returning only data in row 1). Do you know a way around this in access?

Thank you.
 
Upvote 0
I successfully created a search form for my data in access using a unique identifier. I am experiencing the same problem I did in excel before you helped me out (returning only data in row 1). Do you know a way around this in access?

Thank you.
 
Upvote 0
cegbuna said:
I successfully created a search form for my data in access using a unique identifier. I am experiencing the same problem I did in excel before you helped me out (returning only data in row 1). Do you know a way around this in access?

Thank you.
Click to expand...

You should take this question to the Microsoft Access forum on this board.

<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=307><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2104" width=59><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width=54><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2332" width=66><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=59>School</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=54>Game</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=66>Location</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>PID</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64>name 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59>School-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Game-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>P001</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64>name 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59>School-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Game-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>P002</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64>name 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59>School-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Game-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>P003</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64>name 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59>School-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Game-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>P004</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64>name 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=59>School-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 40pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Game-3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=66>Loc-B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>P005</TD></TR></TBODY></TABLE>

PID range in column E is created with:

E2, copied down:

="P"&TEXT(ROWS($E$2:E2),"000")

The sheet housing the above exhibit is imported as Table1:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Table1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Name</TH><TH bgColor=#c0c0c0 borderColor=#000000>School</TH><TH bgColor=#c0c0c0 borderColor=#000000>Game</TH><TH bgColor=#c0c0c0 borderColor=#000000>Location</TH><TH bgColor=#c0c0c0 borderColor=#000000>PID</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-1</TD><TD borderColor=#d0d7e5>Loc-A</TD><TD borderColor=#d0d7e5>P001</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-1</TD><TD borderColor=#d0d7e5>Loc-A</TD><TD borderColor=#d0d7e5>P002</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-2</TD><TD borderColor=#d0d7e5>Loc-A</TD><TD borderColor=#d0d7e5>P003</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>name 2</TD><TD borderColor=#d0d7e5>School-2</TD><TD borderColor=#d0d7e5>Game-2</TD><TD borderColor=#d0d7e5>Loc-B</TD><TD borderColor=#d0d7e5>P004</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>name 2</TD><TD borderColor=#d0d7e5>School-2</TD><TD borderColor=#d0d7e5>Game-3</TD><TD borderColor=#d0d7e5>Loc-B</TD><TD borderColor=#d0d7e5>P005</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

and the foolowing is run:

SELECT Table1.PID, Table1.Name, Table1.School, Table1.Game, Table1.Location
FROM Table1
WHERE (((Table1.Name)="name 1"))
GROUP BY Table1.PID, Table1.Name, Table1.School, Table1.Game, Table1.Location;

with as result:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>PID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Name</TH><TH bgColor=#c0c0c0 borderColor=#000000>School</TH><TH bgColor=#c0c0c0 borderColor=#000000>Game</TH><TH bgColor=#c0c0c0 borderColor=#000000>Location</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>P001</TD><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-1</TD><TD borderColor=#d0d7e5>Loc-A</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>P002</TD><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-1</TD><TD borderColor=#d0d7e5>Loc-A</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>P003</TD><TD borderColor=#d0d7e5>name 1</TD><TD borderColor=#d0d7e5>School-1</TD><TD borderColor=#d0d7e5>Game-2</TD><TD borderColor=#d0d7e5>Loc-A</TD></TR></TBODY><TFOOT></TFOOT></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
Modify system Report
Replies
3
Views
83
Skyybot
Skyybot
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
126
Kaper
Kaper
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B

Forum statistics

Threads
1,224,564
Messages
6,179,544
Members
452,925
Latest member
duyvmex

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