Not sure if vlookup is best for this

[Kiml56]

Hello there i got this problem, that i keep getting stuck on i might be over thinking it, excel version 2016. so here i go . I got this little problem i am trying to get to work.

1. I am trying to get the data from sheet 1 : user ( L 1 ) to go to my sheet 2 Receiver but only have the name go there 1 time as you can see the user name can show several times ( like JR052209 / AZ0602726 ) but i only want them to show up 1 time automatic as showed below so if i was to change one user on sheet1 it would also change on sheet2 .

2. count the instances that a user is listed in sheet1 (L1) and list on sheet2 under lines and also add the numbers on sheet1 (H1 source target qt) per set user (L1) to sheet2 under peices. if any of this is possible.i can attach a copy of the sheet if need be. thanks so much all..

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Kiml56]

Two formula options depending on what version of Excel you have.

+Fluff New.xlsm
	L
1	User
2	E06000008
3	E06000019
4	E06000019
5	E06000025
6	E06000025
7	E06000047
8	E06000050
9	E07000052
10	E07000052
11	E07000068
12	E07000068
13	E07000205
14	E07000205
15	E07000206
16	E07000216
17	E08000017
18	E08000017
19	E09000006
20	E09000012
21	E09000015
22	E09000026
23	E09000031
Sheet1

+Fluff New.xlsm
	A	B
1
2	User	User
3	E06000008	E06000008
4	E06000019	E06000019
5	E06000025	E06000025
6	E06000047	E06000047
7	E06000050	E06000050
8	E07000052	E07000052
9	E07000068	E07000068
10	E07000205	E07000205
11	E07000206	E07000206
12	E07000216	E07000216
13	E08000017	E08000017
14	E09000006	E09000006
15	E09000012	E09000012
16	E09000015	E09000015
17	E09000026	E09000026
18	E09000031	E09000031
19
20
21
Sheet2

Cell Formulas
Range	Formula
A3:A18	A3	=UNIQUE(Sheet1!L2:L23)
B3:B20	B3	=IFERROR(INDEX(Sheet1!$L$2:$L$23,MATCH(0,COUNTIFS(B$2:B2,Sheet1!$L$2:$L$23),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Please update you account details to ahow what version of Excel you are using, as this affects what functions you have available.

Hello Again sorry to be asking again but i seam to be missing one thing i need to find out from sheet1 from L2 how may times one user is listed so it can be counted and entered on sheet2 under lines B2 if possible.. Many thanks again

 

[Fluff]

You can use countif for that.

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...

exceljet.net

 

[Kiml56]

You can use countif for that.

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...

exceljet.net

oh... ok will do thanks

 

[Fluff]

 

[Kiml56]

A few questions
1) What are the sheet name?
2) Do the users in col L start in L2
3) What cell do you want to return the first user in?

Hello Again sorry but can`t get the Countif to do the trick i think you might have misunderstood me i needed it also to be able give me the count like how many times do like if you look on the users on sheet1 you will see some of them listed more than once like AZ060726 that person is listed a total of 90 time on that one sheet and i need total and just show 90 on sheet2 on row B2 (without showing AZ060726 90 times) i have been trying but keep getting weird result none of witch helps

 

[jasonb75]

Where Sheet1!L:L refers to the column with user names in the main sheet and A2 refers to an individual name extracted from the list by Fluff's formula.

=COUNTIF(Sheet1!L:L,A2)

FYI the pivot table would do all of this for you with a lot less effort (example below took 2 minutes from a blank sheet), unless I have misunderstood something, I believe that this is what you are asking for (simplified data format rather than retyping your entire screen capture).

Book3
	A	B	C	D	E	F
1	Source Target qty	User
2	641	SJ		User	Count	Sum
3	683	CP		CP	8	4477
4	353	CP		JR	6	3673
5	164	OH		OH	4	2130
6	548	SJ		SJ	5	3112
7	358	JR		Grand Total	23	13392
8	667	SJ
9	127	CP
10	330	SJ
11	135	CP
12	370	OH
13	650	JR
14	739	CP
15	926	SJ
16	539	JR
17	567	JR
18	977	CP
19	853	OH
20	743	OH
21	683	CP
22	731	JR
23	780	CP
24	828	JR
Sheet2

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1
2	User
3	E06000008	1
4	E06000019	2
5	E06000025	2
6	E06000047	1
7	E06000050	1
8	E07000052	2
9	E07000068	2
10	E07000205	2
11	E07000206	1
12	E07000216	1
13	E08000017	2
14	E09000006	1
15	E09000012	1
16	E09000015	1
17	E09000026	1
18	E09000031	1
19
20
21
22
Sheet2

Cell Formulas
Range	Formula
A3:A20	A3	=IFERROR(INDEX(Sheet1!$L$2:$L$23,MATCH(0,COUNTIFS(A$2:A2,Sheet1!$L$2:$L$23),0)),"")
B3:B20	B3	=IF(A3="","",COUNTIFS(Sheet1!L:L,A3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kiml56]

Where Sheet1!L:L refers to the column with user names in the main sheet and A2 refers to an individual name extracted from the list by Fluff's formula.

=COUNTIF(Sheet1!L:L,A2)

FYI the pivot table would do all of this for you with a lot less effort (example below took 2 minutes from a blank sheet), unless I have misunderstood something, I believe that this is what you are asking for (simplified data format rather than retyping your entire screen capture).

Book3
	A	B	C	D	E	F
1	Source Target qty	User
2	641	SJ		User	Count	Sum
3	683	CP		CP	8	4477
4	353	CP		JR	6	3673
5	164	OH		OH	4	2130
6	548	SJ		SJ	5	3112
7	358	JR		Grand Total	23	13392
8	667	SJ
9	127	CP
10	330	SJ
11	135	CP
12	370	OH
13	650	JR
14	739	CP
15	926	SJ
16	539	JR
17	567	JR
18	977	CP
19	853	OH
20	743	OH
21	683	CP
22	731	JR
23	780	CP
24	828	JR
Sheet2

yep you got it that`s it , I will try this and see if i can make a great looking sheet thanks very much.

 

[Kiml56]

How about

+Fluff New.xlsm
	A	B
1
2	User
3	E06000008	1
4	E06000019	2
5	E06000025	2
6	E06000047	1
7	E06000050	1
8	E07000052	2
9	E07000068	2
10	E07000205	2
11	E07000206	1
12	E07000216	1
13	E08000017	2
14	E09000006	1
15	E09000012	1
16	E09000015	1
17	E09000026	1
18	E09000031	1
19
20
21
22
Sheet2

Cell Formulas
Range	Formula
A3:A20	A3	=IFERROR(INDEX(Sheet1!$L$2:$L$23,MATCH(0,COUNTIFS(A$2:A2,Sheet1!$L$2:$L$23),0)),"")
B3:B20	B3	=IF(A3="","",COUNTIFS(Sheet1!L:L,A3))
Press CTRL+SHIFT+ENTER to enter array formulas.

great this works also now i got a few ideas to work with you all have been a great help maybe now i can get started again...