Tricky Mod Function

[duckinbath]

I need to create a new columin my work sheet using a mod function that sorts the last digit of a number. I cannot use Excel Sort or Filter the column looks like this except much longer. The last digit I'm trying to filter is 3.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" 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=xl67 height=20 width=64>1110001</TD></TR><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=xl67 height=20 width=64>1110002</TD></TR><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=xl67 height=20 width=64>1110003</TD></TR><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=xl67 height=20 width=64>1110004</TD></TR><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=xl67 height=20 width=64>1110005</TD></TR><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=xl67 height=20 width=64>1110006</TD></TR><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=xl67 height=20 width=64>1110007</TD></TR></TBODY></TABLE>
I would appreciate any help thank you.

 

[duckinbath]

oh I didnt read that post but yes it did thank you!

 

[Aladin Akyurek]

yeah there is only one 3 in my previous posts but I only posted a few of the ID's there are 357 ID's that I need to sort through.

If you just want to flag them...

D2:

=(RIGHT(A2)+0=3)+0

would yield a 1 for a hit, a 0 for a miss.

Note that using MOD you would get all ID's with 0 or a multiple of 3
as last digit. Just try the following to see that:

D2:

=(MOD(RIGHT(A2)+0,3)=0)+0

Again: 1 for a hit, 0 for a miss.

Note. If you want to list all records where ID's end in 3
in a separate result area, a different approach is needed.

 

[Just Jan]

ID nos in col E
F1 =IF(VALUE(RIGHT(E1,1))=3,ROW(),"") copy down list
G1 =INDEX(E:E,MATCH(SMALL(F:F,ROW(F1)),F:F,0)) copy down until NUM error appears

HTH

 

[Peter_SSs]

ID nos in col E
F1 =IF(VALUE(RIGHT(E1,1))=3,ROW(),"") copy down list
G1 =INDEX(E:E,MATCH(SMALL(F:F,ROW(F1)),F:F,0)) copy down until NUM error appears

HTH

Your use of the ROW function in column G is risky. If the user decides, say, to add a new heading row by inserting a new row 1 after your formula have been added, your list will lose the first entry.

If using a helper column to make the list, this would be more robust. It also copes with ending the list neatly. E1 houses a 0. E2 and G2 copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	StudentID	Exam	Version			3	List
2	1110001	1	A		0		1110003
3	1110002	1	C		0		1110863
4	1110003	1	A		1		1110893
5	1110004	1	A		1
6	1110863	1	A		2
7	1110893	1	A		3
8	1110007	1	A		3
9
List

 

[Just Jan]

Your use of the ROW function in column G is risky. If the user decides, say, to add a new heading row by inserting a new row 1 after your formula have been added, your list will lose the first entry.

If using a helper column to make the list, this would be more robust. It also copes with ending the list neatly. E1 houses a 0. E2 and G2 copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	StudentID	Exam	Version			3	List
2	1110001	1	A		0		1110003
3	1110002	1	C		0		1110863
4	1110003	1	A		1		1110893
5	1110004	1	A		1
6	1110863	1	A		2
7	1110893	1	A		3
8	1110007	1	A		3
9
List

Sorry about delay in replying, other matters pressing but thank you for your solution. I've never used ROWS before but will keep in mind for later usage. Also usually use an IF(ISERROR( statement to blank out #NUM! values in lists but didn't this time.

As you can see I'm a newbie to this forum and am still feeling my way around. Thanks again>

 

[Peter_SSs]

As you can see I'm a newbie to this forum and am still feeling my way around.

That's no problem - we were all that at some stage.