Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared

Take a look at DataFilterAutofilter. It does exactly what you want (and a lot
more), although you select the ID from a drop down list, not by entering it in a
cell.

If you really want to have the selected ID entered, it would be a simple macro
to create.

--
Regards,
Fred


"jared" wrote in message
...
Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared

The easiest way to do it is to use the AutoFilter.

Select the column header ID NUMBER
Goto the menu DateFilter AutoFilter
Click the drop arrow and then select the ID number of interest.

To remove the filter just goto the menu DataFilterAutofilter. It toggles
on/off just by selecting it.

This could also be done using formulas but it's more complicated and may not
be the best solution if you have 1000's of rows of data.

Biff

"jared" wrote in message
...
Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below
is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared

Do you think this will work?

Yes.

Do you think my above array formula will grind the system to a halt?

No.

Did you have the same formula in mind?

No. I had something completely different in mind. Not everyone likes to or
can use filters (me included) or, you might need this data on another sheet
AND you want it to be dynamic (not filter then copy/paste). (one of the
reasons I don't like filters) Then what do you do?

You do this:

Sample file: Extract_data 19kb

http://cjoint.com/?jxgj2rCtO0

The file is based on this thread. Select an ID number from the drop down.
The highlighted area is where the formulas are located. Actually, it's one
formula copied to all the cells.

For a detailed explanation see this:

http://tinyurl.com/j2x2w

Biff

"Epinn" wrote in message
...
Biff,

If I understand correctly, with AutoFilter custom we can only select a
maximum of two ID's at a time. Am I correct? If Jared wants to see all
ID's that are duplicated, can he try the following?

Sort column A that contains the ID number.

Put the formula in a blank column, say G.

=IF(COUNTIF(A:A,A2)1,"Duplicate","")

Please note that the formula is an array formula i.e. must be entered with
ctrl+shift+enter.

The argument for COUNTIF is A2 assuming row 1 is the column heading and A2
is the first piece of data. Adjust accordingly based on the number of rows
for headers.

Fill column G with the formula.

Then do AutoFilter to select "Duplicate" in column G.

Then sort on column A, ID no. again. Not really necessary.

Do you think this will work?

<<This could also be done using formulas but it's more complicated and may
not
be the best solution if you have 1000's of rows of data.

Did you have the same formula in mind? Do you think my above array formula
will grind the system to a halt?

Appreciate guidance.

Epinn

"Biff" wrote in message
...
The easiest way to do it is to use the AutoFilter.

Select the column header ID NUMBER
Goto the menu DateFilter AutoFilter
Click the drop arrow and then select the ID number of interest.

To remove the filter just goto the menu DataFilterAutofilter. It toggles
on/off just by selecting it.

This could also be done using formulas but it's more complicated and may not
be the best solution if you have 1000's of rows of data.

Biff

"jared" wrote in message
...
Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below
is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared

Ooops!

I posted the wrong sample file!

Try this one:

http://cjoint.com/?jxhE0xClWR

Biff

"Biff" wrote in message
...
Do you think this will work?

Yes.

Do you think my above array formula will grind the system to a halt?

No.

Did you have the same formula in mind?

No. I had something completely different in mind. Not everyone likes to or
can use filters (me included) or, you might need this data on another
sheet AND you want it to be dynamic (not filter then copy/paste). (one of
the reasons I don't like filters) Then what do you do?

You do this:

Sample file: Extract_data 19kb

http://cjoint.com/?jxgj2rCtO0

The file is based on this thread. Select an ID number from the drop down.
The highlighted area is where the formulas are located. Actually, it's one
formula copied to all the cells.

For a detailed explanation see this:

http://tinyurl.com/j2x2w

Biff

"Epinn" wrote in message
...
Biff,

If I understand correctly, with AutoFilter custom we can only select a
maximum of two ID's at a time. Am I correct? If Jared wants to see all
ID's that are duplicated, can he try the following?

Sort column A that contains the ID number.

Put the formula in a blank column, say G.

=IF(COUNTIF(A:A,A2)1,"Duplicate","")

Please note that the formula is an array formula i.e. must be entered with
ctrl+shift+enter.

The argument for COUNTIF is A2 assuming row 1 is the column heading and A2
is the first piece of data. Adjust accordingly based on the number of
rows for headers.

Fill column G with the formula.

Then do AutoFilter to select "Duplicate" in column G.

Then sort on column A, ID no. again. Not really necessary.

Do you think this will work?

<<This could also be done using formulas but it's more complicated and may
not
be the best solution if you have 1000's of rows of data.

Did you have the same formula in mind? Do you think my above array
formula will grind the system to a halt?

Appreciate guidance.

Epinn

"Biff" wrote in message
...
The easiest way to do it is to use the AutoFilter.

Select the column header ID NUMBER
Goto the menu DateFilter AutoFilter
Click the drop arrow and then select the ID number of interest.

To remove the filter just goto the menu DataFilterAutofilter. It toggles
on/off just by selecting it.

This could also be done using formulas but it's more complicated and may
not
be the best solution if you have 1000's of rows of data.

Biff

"jared" wrote in message
...
Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below
is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared

Biff,

Now I truly understand what you meant by using formulas. So, no DATA menu period. There are probably more formulas if we want to have a list of all the duplicates.

Thank you for taking the time typing up the sample and posting the explanation. I kind of understand the overall idea and I should recheck this again when I am more experienced. For some reason, SMALL ( ) as it is in the formula doesn't quite click with me. I see that we have done the logical test and that the calculation of the row number is performed. I can't quite grasp the role of SMALL ( ). Out of curiosity, I took out SMALL in the formula and I thought the formula wouldn't work at all. To my surprise, it still pulled out the right records. I selected 1000218 in the drop down list. I saw most of the info that I was supposed to see i.e. it displayed three records. The only problem was the first "Mandy" didn't show. The error was #VALUE! I don't know what is going on. But I don't want to take up your time. I'll let it go.

I am more interested in knowing more about sorting and filtering. The other day, you mentioned that it is a good idea to sort a huge table used by VLOOKUP. Later, Dave suggested that it is undesirable to sort the list when we try to do advanced filter. Obviously, for this formula, we don't need to sort. Is it true that when we deal with lists, regardless of whether we do filter, we don't usually sort?

By the way, I agree with everything you said about going to classes and learning from books. My experience of learning MS at the college was miserable. I had to be very careful that I didn't allow the wrong information to get into my system. I wish I found the forums long ago.

Appreciate your guidance.

Epinn

"Biff" wrote in message ...
Do you think this will work?

Yes.

Do you think my above array formula will grind the system to a halt?

No.

Did you have the same formula in mind?

No. I had something completely different in mind. Not everyone likes to or
can use filters (me included) or, you might need this data on another sheet
AND you want it to be dynamic (not filter then copy/paste). (one of the
reasons I don't like filters) Then what do you do?

You do this:

Sample file: Extract_data 19kb

http://cjoint.com/?jxgj2rCtO0

The file is based on this thread. Select an ID number from the drop down.
The highlighted area is where the formulas are located. Actually, it's one
formula copied to all the cells.

For a detailed explanation see this:

http://tinyurl.com/j2x2w

Biff

"Epinn" wrote in message
...
Biff,

If I understand correctly, with AutoFilter custom we can only select a
maximum of two ID's at a time. Am I correct? If Jared wants to see all
ID's that are duplicated, can he try the following?

Sort column A that contains the ID number.

Put the formula in a blank column, say G.

=IF(COUNTIF(A:A,A2)1,"Duplicate","")

Please note that the formula is an array formula i.e. must be entered with
ctrl+shift+enter.

The argument for COUNTIF is A2 assuming row 1 is the column heading and A2
is the first piece of data. Adjust accordingly based on the number of rows
for headers.

Fill column G with the formula.

Then do AutoFilter to select "Duplicate" in column G.

Then sort on column A, ID no. again. Not really necessary.

Do you think this will work?

<<This could also be done using formulas but it's more complicated and may
not
be the best solution if you have 1000's of rows of data.

Did you have the same formula in mind? Do you think my above array formula
will grind the system to a halt?

Appreciate guidance.

Epinn

"Biff" wrote in message
...
The easiest way to do it is to use the AutoFilter.

Select the column header ID NUMBER
Goto the menu DateFilter AutoFilter
Click the drop arrow and then select the ID number of interest.

To remove the filter just goto the menu DataFilterAutofilter. It toggles
on/off just by selecting it.

This could also be done using formulas but it's more complicated and may not
be the best solution if you have 1000's of rows of data.

Biff

"jared" wrote in message
...
Good day!
I want to make a function formula instructing to list down all the same
records based on the inputed by the user, the thing I can't solve. Below
is
the table example.

Table:
A B C D
1 ID NUMBER NAME Course Points
2 1000218 Mandy BS Computer Science 5
3 1000214 Jehu BS Nursing 7
4 1000215 Hernan Civil Engineering 8
5 1000216 Jomar Information Technology 2
6 1000217 Apple Associate in C.S. 4
7 1000218 Mandy BS Computer Science 9
8 1000219 Ermin BS Nursing 7
9 1000218 Mandy BS Computer Science 8
10 1000221 Jay Civil Engineering 3

Sample output result that i want to come out:

input id number: 1000218

1000218 Mandy BS Computer Science 5
1000218 Mandy BS Computer Science 9
1000218 Mandy BS Computer Science 8

Is this possible to do?

Your help is very much appreciated.
Jared