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]

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?

 

[jasonb75]

That would be best done with a pivot table.

Rows:- User
Value:- Count of User
Value:- Sum of Source Target qty

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

 

[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?

1. this i am work on right now is called 6-30-2020 Final.xlsx all i change every month will be the first part so like 6 will be 7 or 8 and so on 8 if i get it to work right.
2. yes user start in L2 .
3. for now A3 then i plane to only use up to A28

hope this give you a better idea thanks so much.

 

[Kiml56]

That would be best done with a pivot table.

Rows:- User
Value:- Count of User
Value:- Sum of Source Target qty

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

will this up automatic if line gets deleted or added

 

[jasonb75]

will this up automatic if line gets deleted or added

Not by default, you can set it to refresh when you open your workbook or use vba to refresh it when you perform a specific action such as adding or changing data, or saving your workbook.

Personally, I would do it on save (or even refresh manually which is very simple anyway), that way you will prevent the lag of constant refreshing (or calculation if you use formulas) when you are adding more data.

Refresh PivotTable data - Microsoft Support

Manually refresh or update data in a PivotTable that's connected to an external data source to see changes that were made to that data, or refresh it automatically when opening the workbook.

support.microsoft.com

 

[Fluff]

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.

 

[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.

 

[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.

thanks the first one worked fine for part one i am using excel 2016

 

[Kiml56]

Not by default, you can set it to refresh when you open your workbook or use vba to refresh it when you perform a specific action such as adding or changing data, or saving your workbook.

Personally, I would do it on save (or even refresh manually which is very simple anyway), that way you will prevent the lag of constant refreshing (or calculation if you use formulas) when you are adding more data.

Refresh PivotTable data - Microsoft Support

Manually refresh or update data in a PivotTable that's connected to an external data source to see changes that were made to that data, or refresh it automatically when opening the workbook.

support.microsoft.com

i will look in to this one also thanks