Index Preparation

[adiles]

Employee Names

Supplier

Total

IN

OUT

IN

OUT

IN

OUT

JOB

Total

IN

OUT

IN

OUT

IN

OUT

JOB

Total

IN

OUT

IN

OUT

IN

OUT

JOB

Talib

Saleem

14​

23.5​

10076​

9.5​

14​

24​

0​

6​

10076​

16​

Qasim

Saleem

14​

23.5​

10076​

9.5​

14​

24​

0​

1​

10076​

11​

15​

18.5​

GHMJ242

Manzoor

Saleem

10015

0​

14​

24​

0​

6​

10106​

16​

15​

19.5​

GHMJ242

Saleem's Employee 4

Saleem

9675

0​

9545

0​

9889

Hi All,
This is my table of data and I want an Index to understand easily what's inside this long table. I want to display all the job numbers without duplicates in a new table of index, which will add automatically if I add new job numbers.
Example

Job	Total
10076	I will do sumif if i get job numbers without duplicates
10015
9889
9675
GHMJ242

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Employee Names	Supplier	Total	IN	OUT	IN	OUT	IN	OUT	JOB	Total	IN	OUT	IN	OUT	IN	OUT	JOB	Total	IN	OUT	IN	OUT	IN	OUT	JOB
2	Talib	Saleem				14	23.5			10076	9.5			14	24	0	6	10076	16
3	Qasim	Saleem				14	23.5			10076	9.5			14	24	0	1	10076	11			15	18.5			GHMJ242
4	Manzoor	Saleem								10015	0			14	24	0	6	10106	16			15	19.5			GHMJ242
5	Saleem's Employee 4	Saleem								9675	0							9545	0							9889
6
7	Job	Total
8	10076	19
9	GHMJ242	27
10	10015	0
11	10106	0
12	9675	0
13	9545	0
14	9889	0
15
Sheet15

Cell Formulas
Range	Formula
B8:B14	B8	=SUM(IFERROR($C$2:$Z$5*($C$1:$Z$1="total")*($J$2:$AG$5=A8),0))
A8:A15	A8	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($C$2:$Z$5)*100+COLUMN($C$2:$Z$5))/(COUNTIF(A$7:A7,$C$2:$Z$5)=0)/($C$2:$Z$5<>"")/($C$1:$Z$1="JOB"),1),"R00C00"),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[adiles]

I am getting a value "IN" when i apply this formula. Could you please tell me is there anything i have to change in AGGREGATE function. Actually i didn't know what the 15,6 in AGGREGATE refer to. Thanks for your effort by the way.

 

[Eric W]

AGGREGATE is a composite function which can perform many different functions (SUM, COUNT, MAX, etc.), but with the added ability to skip errors. In this case, the 15 refers to the SMALL function, and the 6 means to ignore errors. If you're getting IN as a response, that probably means you've changed the ranges in the function incorrectly:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($C$2:$Z$5)*100+COLUMN($C$2:$Z$5))/(COUNTIF(A$7:A7,$C$2:$Z$5)=0)/($C$2:$Z$5<>"")/($C$1:$Z$1="JOB"),1),"R00C00"),0),"")

The 2's in red refer to the top row of data, while the 1's in green refer to the header row. The 5's refer to the last row of data, and the A$7:A7 refers to the cell right above the formula.

 

[adiles]

AGGREGATE is a composite function which can perform many different functions (SUM, COUNT, MAX, etc.), but with the added ability to skip errors. In this case, the 15 refers to the SMALL function, and the 6 means to ignore errors. If you're getting IN as a response, that probably means you've changed the ranges in the function incorrectly:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($C$2:$Z$5)*100+COLUMN($C$2:$Z$5))/(COUNTIF(A$7:A7,$C$2:$Z$5)=0)/($C$2:$Z$5<>"")/($C$1:$Z$1="JOB"),1),"R00C00"),0),"")

The 2's in red refer to the top row of data, while the 1's in green refer to the header row. The 5's refer to the last row of data, and the A$7:A7 refers to the cell right above the formula.

Thanks alot. Now it works. But I am not able to apply the same formula in another sheet, which means I have the data in Sheet1 and I want to apply the formula in Sheet2.
Is it possible ?

 

[Eric W]

You should be able to do that. Add a sheet reference to all the ranges except the cell above the formula:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(Sheet1!$C$2:$Z$5)*100+COLUMN(Sheet1!$C$2:$Z$5))/(COUNTIF(A$7:A7,Sheet1!$C$2:$Z$5)=0)/(Sheet1!$C$2:$Z$5<>"")/(Sheet1!$C$1:$Z$1="JOB"),1),"R00C00"),0),"")

 

[adiles]

You should be able to do that. Add a sheet reference to all the ranges except the cell above the formula:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(Sheet1!$C$2:$Z$5)*100+COLUMN(Sheet1!$C$2:$Z$5))/(COUNTIF(A$7:A7,Sheet1!$C$2:$Z$5)=0)/(Sheet1!$C$2:$Z$5<>"")/(Sheet1!$C$1:$Z$1="JOB"),1),"R00C00"),0),"")

Thank you so much