List of names

[alliswell]

In excel 2007
I have a database and in column A i have names database. I want to list the name of sam in column G to know how much times he had purchased goods

Thanks

 

[AlanY]

something like this?

Book1
	A	B	C	D	E	F	G	H
1
2	Name 5						Name 5	1
3	Name 1						Name 1	4
4	Name 3						Name 3	8
5	Name 3						Name 2	2
6	Name 3						Name 4	4
7	Name 1
8	Name 3
9	Name 3
10	Name 1
11	Name 3
12	Name 2
13	Name 2
14	Name 4
15	Name 4
16	Name 4
17	Name 3
18	Name 1
19	Name 4
20	Name 3
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($G$1:G1, $A$2:$A$100), 0)), "")
H2:H6	H2	=IF(G2<>"", COUNTIF($A$2:$A$100, G2), "")
A2:A20	A2	="Name "& RANDBETWEEN(1,5)

 

[etaf]

you may be able to use a countif()
but i dont know how the data is layed out

i assume in column A - sam will appear each time he/shee makes a purchase - or do you have a quantity column ??

just to count how many times sam appears in column A

in column G put

=Countif(A1:A10000, "sam")
change the range to match the rows or more

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[alliswell]

something like this?

Book1
	A	B	C	D	E	F	G	H
1
2	Name 5						Name 5	1
3	Name 1						Name 1	4
4	Name 3						Name 3	8
5	Name 3						Name 2	2
6	Name 3						Name 4	4
7	Name 1
8	Name 3
9	Name 3
10	Name 1
11	Name 3
12	Name 2
13	Name 2
14	Name 4
15	Name 4
16	Name 4
17	Name 3
18	Name 1
19	Name 4
20	Name 3
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($G$1:G1, $A$2:$A$100), 0)), "")
H2:H6	H2	=IF(G2<>"", COUNTIF($A$2:$A$100, G2), "")
A2:A20	A2	="Name "& RANDBETWEEN(1,5)

Assume i have data in column A, in g1 i will type name of person for example sam, then excel must search number of sam in column A and show in column g2:g100 suppose excel found 6 sam so it must show 6 sam in column g2:g100
Sam
Sam
Sam
Sam
Sam
Sam

 

[AlanY]

ok, try this

Book1
	A	B	C	D	E	F	G
1	sam						sam
2	sam						sam
3	sam						sam
4	sam						sam
5	other						sam
6	sam						sam
7	other						sam
8	sam						sam
9	sam						sam
10	sam
11	other
12	other
13	other
14	other
15	sam
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(ROW(A2)<=COUNTIF(A:A,$G$1),$G$1,"")

 

[alliswell]

ok, try this

Book1
	A	B	C	D	E	F	G
1	sam						sam
2	sam						sam
3	sam						sam
4	sam						sam
5	other						sam
6	sam						sam
7	other						sam
8	sam						sam
9	sam						sam
10	sam
11	other
12	other
13	other
14	other
15	sam
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(ROW(A2)<=COUNTIF(A:A,$G$1),$G$1,"")

Thanks alan. Its working but a minor mistake. In column G when i copy n drag formula downwards, it displays only 8 sams instead of 9 excluding G1. If it includes G1 too then its perfect. But suppose i put my lookup cell in b1 and results in column G, then it will display only 8 sams instead of 9. It must exclude g1 and give results 9 sams. Thankyou

 

[Sufiyan97]

Try

Credit @AlanY

Book6
	A	B	C	D	E	F	G	H
1	sam						sam
2	sam						sam
3	sam						sam
4	sam						sam
5	other						sam
6	sam						sam
7	other						sam
8	sam						sam
9	sam						sam
10	sam						sam
11	other
12	other
13	other
14	other
15	sam
16
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(ROW(A2)<=COUNTIF(A:A,$G$1)+1,$G$1,"")

 

[alliswell]

ok, try this

Book1
	A	B	C	D	E	F	G
1	sam						sam
2	sam						sam
3	sam						sam
4	sam						sam
5	other						sam
6	sam						sam
7	other						sam
8	sam						sam
9	sam						sam
10	sam
11	other
12	other
13	other
14	other
15	sam
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(ROW(A2)<=COUNTIF(A:A,$G$1),$G$1,"")

Thanks alan. Its working but a minor mistake. In column G when i copy n drag formula downwards, it displays only 8 sams instead of 9 excluding G1. If it includes G1 too then its perfect. But suppose i put my lookup cell in b1 and results in column G, then it will display only 8 sams instead of 9. It must exclude g1 and give

Try

Credit @AlanY

Book6
	A	B	C	D	E	F	G	H
1	sam						sam
2	sam						sam
3	sam						sam
4	sam						sam
5	other						sam
6	sam						sam
7	other						sam
8	sam						sam
9	sam						sam
10	sam						sam
11	other
12	other
13	other
14	other
15	sam
16
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(ROW(A2)<=COUNTIF(A:A,$G$1)+1,$G$1,"")

Thanks guys its working.

 

[Sufiyan97]

You're welcome.
Glad we could help!