index match with repetitive lookup value

Microsoft Office excel 2007 home & stude...
January 5, 2011 at 09:03:05
Specs: Windows Vista
am creating an excel file the should return all the subjects taken by every student, the problem is when i apply index math it retrieves the required subjects but it proceeds to subjects taken by next student, am using the student id as lookup value. how can i stop the search after the last subject?
thanks

See More: index match with repetitive lookup value

Report •


#1
January 5, 2011 at 09:22:30
Keep in mind that we can't see your spreadsheet from where we're sitting.

Therefore we don't know how your data is laid out or what "index math" (sic) formula you are using.

Please post some example data, the formula you are using and the desired output based on the example data.

Please read the How To mentioned in my signature line before posting your data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 7, 2011 at 10:16:53
this is the data:
ID Course Edited Grade
900001753 ACC 201 C-
900001753 ACC 202 B
900001753 ARA C+
900001753 BIS 201 C-
900001753 CHM 103 A-
900001753 COM 101 A
900001753 COM 102 C
900001753 ECO 201 B+
900001753 ECO 202 B
900001753 ECO 301 B+
900001753 ENG 225 B+
900001753 FIN 201 A
900001753 MGT 201 A
900001753 MIS 200 B
900001753 MIS 201 B
900001753 MIS 301 C+
900001753 MKT 201 C
900001753 MKT 301 A-
900001753 MTH 101 C
900001753 MTH 102 B+
900001753 PHY 100 B
900001753 QAN 202 B
900001753 QBA 202 B-
900001753 SIE P3 B+
900005518 ACC 201 B-
900005518 ACC 202 B-
900005518 BIS 101 B-
900005518 BIS 201 B+
900005518 BLW 301 A-
900005518 BUS 300 B-
this is the formula am using:
=INDEX('Data 1'!A2:D17807,MATCH($B$1,'Data 1'!A2:A17807,0),3)
note that user should enter the id in B1 and then subjects from sheet "Data 1" will be return. I just need to know how to stop the loop!
many thanks

Report •

#3
January 7, 2011 at 10:50:48
It appears that you did not follow the instructions from my previous post:

Please read the How To mentioned in my signature line before posting your data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 7, 2011 at 11:38:18
   ID	        Course	Grade
900001753	ACC 201	C-
900001753	ACC 202	B
900001753	ARA 	C+
900001753	BIS 201	C-
900005518	QAN 201	A-
900005518	QBA 201	A
900005518	QBA 202	A-
900005518	SOC 201	B
900007161	ACC 201	A
900007161	ACC 202	C-
900007161	ARA 	C+
900007161	BIS 201	C
900010049	MTH 203	C
900010049	QAN 201	B
900010049	QBA 202	B+
900010680	ACC 301	C-
900010680	ARA 102	C
900010680	BIS 101	B

to retrieve the subject:
=INDEX('Data 1'!A2:D17807,MATCH($B$1,'Data 1'!A2:A17807,0),3)

to retrive the grade
=INDEX('Data 1'!B2:E17807,MATCH($B$1,'Data 1'!A2:A17807,0),3)


Report •

#5
January 7, 2011 at 12:56:13
That's a bit better but...

1 - 694 lines of data is a bit excessive. It makes reading the thread a bit difficult. I've trimmed it down a bit.

2 - You didn't include Column Letters or Row Numbers, so at this point we'll have to guess at those.

3 - It appears that there is a space missing between your ID and Course columns. Should it look like this?

       A          B         C
1     ID       Course     Grade
2  900001753   ACC 201     C-
3  900001753   ACC 202     B
4  900001753   ARA         C+
5  900001753   BIS 201     C-
6  900005518   ACC 201     B-
7  900005518   ACC 202     B-
8  900005518   BIS 101     B-
9  900005518   BIS 201     B+
10 900007161   ACC 201     A
11 900007161   ACC 202     C-
12 900007161   ARA         C+
13 900007161   BIS 201     C
14 900007161   BLW 301     B

4 - You gave us the formulas that you are using, but not an example of what you expect the formulas to return or what is really the problem.

Tell us what your formulas are returning and why that isn't what you expect.

Keep this in mind: You need our help, and we want to help you, so the easier you make it for us to understand exactly what the issues are the more likely that we will stick with you until you get the answer you are looking for. If we have to expend half our energy just figuring out what you have/want, that's that much less energy we have to solve your issue.

For the most part, we can't just answer your question without actually setting up a spreadsheet to test possible solutions, and if we're starting with the wrong layout, not only can't we test your formulas, but nothing we suggest will make sense to you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 7, 2011 at 20:48:21
I have the following data in a sheet called Data 1:
A                                 B              C                           D
ID	                 Course	   Edited	                Grade
1   900001753 	ACC201	   ACC 201	C-
2   900001753 	ACC202	   ACC 202	B
3   900001753	ARA	   ARA 	                C+
4   900001753 	BIS201	   BIS 201	                C-
5   900001753 	CHM103	   CHM 103	A-
6   900001753	COM101	   COM 101	A
7   900001753	COM102	   COM 102	C
8   900001753 	ECO201	   ECO 201	B+
9   900001753 	ECO202	   ECO 202	B
10 900001753	ECO301	   ECO 301	B+
11 900001753 	ENG225	   ENG 225	B+
12 900001753	FIN201	   FIN 201	                A
13 900001753 	MGT201	   MGT 201	A
14 900001753	MIS200	   MIS 200	                B
15 900001753 	MIS201	   MIS 201	                B

I have added column C to separate the digits and characters using this function:
=LEFT(B2,3)&" "&RIGHT(B2,LEN(B2)-3)

now in another sheet called Main, I made the interface as an academic advising system so that when the advisor types the student Id in cell B1 all the courses belong to that ID should be returned with the corresponding grade.
am using these formulas to return back the courses and the corressponding grades:
=INDEX('Data 1'!A2:D17807,MATCH($B$1,'Data 1'!A2:A17807,0),3)
=INDEX('Data 1'!A2:D17807,MATCH($B$1,'Data 1'!A2:A17807,0),4)

and this was the output:
Course	Grade
BIS 101	C
ECO 201	A
MTH 101	B
QBA 201	B-
WRI 102	C+
BIS 101	B+
ECO 201	B+
QBA 201	C-
WRI 102	B+
BIS 101	C+
ECO 201	B-

as you can see there are 2 problems here:
1- the courses are repeated again and again if i drag the formula down.
2- the grades are not belong to the courses taken by that particular ID.
if this is still confusing, can i email the file to you or if there is any way here to attach it?
thanks alot for your great support.

Report •

#7
January 8, 2011 at 20:20:49
Try this technique, which is usually used to find the nth occurrence of a value in a list, but fits this circumstance also.

Insert a new Column A in Data 1 (you can hide it later if you want)
Place this formula in the new A2 and drag it down. Carefully note where the $ are used.

=B2 & "_" & COUNTIF($B$2:B2, B2)

This should give you a list ID's with a number next to them:

     A              B           C          D     E
1                    ID		        Course	 Grade
2  900001753_1	900001753    ACC201	ACC 201	 C-
3  900001753_2	900001753    ACC202	ACC 202	 B
4  900001753_3	900001753    ARA        ARA      C+
5  900001753_4	900001753    BIS200	BIS 201	 C-
6  900005518_1	900005518    ACC201	ACC 201	 B-
7  900005518_2	900005518    ACC202	ACC 202	 B-
8  900005518_3	900005518    BIS101	BIS 101	 B-
9  900005518_4	900005518    BIS201	BIS 201	 B+
10 900007161_1	900007161    ACC201	ACC 201	 A
11 900007161_2	900007161    ACC202	ACC 202	 C-
12 900007161_3	900007161    ARA        ARA      C+
13 900007161_4	900007161    BIS200	BIS 201	 C
14 900007161_5	900007161    BLW300     BLW 301  B


Now, on your Main sheet, you can use VLOOKUP to look up the individual values in Column A by using the ROW() function to tack the extra numbers onto the end of the value entered in B1.

Assuming the formula starts in Row 2 on the Main sheet, use this to return the Course number, dragging it down as far as you think you need to:

=IF(ISNA(VLOOKUP($B$1&"_"&ROW()-1, 'Data 1'!$A$2:$E$14,4,0)), "",
VLOOKUP($B$1&"_"&ROW()-1, 'Data 1'!$A$2:$E$14,4,0))

If the formula starts in a Row other than 2, simply adjust the ROW()-1 portion so that the ROW() portion returns 1 in that first cell.

One final note:

You said: "when the advisor types the student Id in cell B1"

I seriously suggest that you create a Data Validation list in Main!B1 using the list of ID's from Data 1 Column B (after inserting the new Column A).

If you choose the Unique Records option you'll get a single occurrence of each ID that the adviser can then choose instead of type.

This eliminates the possibility of typos.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 10, 2011 at 05:00:58
it works, thanks a lot

Report •

Ask Question