Only Brings Unique Value Across

[ChetanPuri]

Hi Excel Team,

Please see below, I have 2 worksheets, Detailed Report and APLoadData. In Column S on Detailed Worksheet I have Description Column , S, and on APUPLOAD Data, in Column E6 onwards, I just want the unique value gets upload from the Detailed report to APupload, just wondering if there is a vBA or a formula to do this every month. Any help would be appreciated

Book1
	S
1	Description
2	H105 SIP-Landlines-
3	86 Pruen Rd SIP Phone Rental-Landlines-
4	NTF Coolalinga SIP-Landlines-
5	SIP NTF Berrimah-Landlines-
6	Lachlan Michell-Mobiles-
7	Connor Newble Mobile-Mobiles-
8	Connor Newble Mobile-Mobiles-
9	Frontier Guest Lift phone (mobile)-Mobiles-
10	Hosp Central Reservations-Landlines-
11	Hosp Central Reservations-Landlines-
12	Lachlan Michell - mobile-Mobiles-
13	Jay Mcneice-Mobiles-
14	NAB Lifts phone (mobile)-Mobiles-
15	NAB Lifts phone (mobile)-Mobiles-
16	H-Hotel Lift phone (mobile)-Mobiles-
17	H Hotel Lift 1 33960-Landlines-
18	Novotel Lift Phone (mobile)-Mobiles-
19	Frontier Service Lift phone (mobile)-Mobiles-
20	H-Hotel Lift phone (mobile)-Mobiles-
21	Frontier Guest Lift phone (mobile)-Mobiles-
22	NAB Lifts phone (mobile)-Mobiles-
23	Hosp Central Reservations-Landlines-
24	Hosp Central Reservations-Landlines-
25	Kristy Millar-Mobiles-
26	SS TELSTRA MANAGED RADIUS IP Wireless Port-Shared Services-
27	CDU additional SIM-Mobiles-
28	ICT 1300 number-Landlines-
29	Terry Cox - internet wireless-Data-
30	Terry Cox - internet wireless-Data-
31	D Mullen old iPad-Internet - Wireless-
32	Eddie Godwin Mobile Data-Internet - Wireless-
33	Jenny Dignan iPad-iPad-
34	Shane Dignan iPad-iPad-
35	DARREN MULLAN-iPad-
36	B Brunker ipad-iPad-
37	Eddie Goodwin iPad-iPad-
38	Mark Pattison iPad (SEQ Auditor)-iPAD-
39	John Christie ON HOLD-Mobiles-
40	Roofing Scott Blacklock iPad-Mobiles-
41	Shane Trainer iPad-Mobiles-
42	Shane Trainer iPad-Mobiles-
Detail_report (9)

Cell Formulas
Range	Formula
S2:S42	S2	=TEXTJOIN("-",FALSE,K2,H2,E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S:S	Cell Value	duplicates	text	NO

Book1
	E
4	Invoice Description
5	30 A (M)
6	IF(ROWS(E6:E$99)>$E$1,"",LET( filtered_data, FILTER('Detail_report (9)'!$S$2:$S$19000,'Detail_report (9)'!$T$2:$T$19000=APLODATA!$E$3), unique_vals, UNIQUE(filtered_data),unique_vals, UNIQUE(filtered_data),row_num, ROWS(APLODATA!E6:E$99), IF(row_num <= COUNTA(unique_vals), INDEX(unique_vals, row_num), "")))
7	DEV CDU SITE MOBILE-Mobiles-
8	CDU Mobile Nick Swifte-Mobiles-
9	Mark Pattison-iPAD-
10	Maelys Koenig-Internet - Wireless-
11	Jenny Dignan 100372 4G-Internet - Wireless-
12	Caretaking - Yianni Kaisarlis-Caretaker C2-
13	Akoya Caretaker #TO BE CANCELLED-Caretaker AKOYA-
14	Caretaking - Bruce Walker-Caretaker 130-
15	Caretaking - Yianni Kaisarlis-Caretaker 130-
16	Connect IP Backup NTF Berrimah-Telstra Legacy charges-
17	Connect IP Backup Frontier-Telstra Legacy charges-
18	Connect IP Backup H105-Telstra Legacy charges-
19	Connect IP Backup NTF Coolalinga-Telstra Legacy charges-
20	Connect IP Backup H-Hotel-Telstra Legacy charges-
21	Connect IP Backup Novotel-Telstra Legacy charges-
22	Connect IP Backup CDC-Telstra Legacy charges-
23	Connect IP Backup 84 Pruen Rd-Telstra Legacy charges-
24	Nick Swifte - mobile-Mobiles-
25	HPL IPAD MATTHEW SHARP-Mobiles-
26	Anthony Tonkin - iPad #TBC-Mobiles-
27	Aakar Achraya - Mobile-Mobiles-
28	Jermaine Kidney ( iPad)-Mobiles-
29	DEV iPad Adam Worthy-Mobiles-
30	DEV iPad Jay Mcneice-Mobiles-
31	CDU Ali Zayif iPad-Mobiles-
32	CDU mobile Rohit Adhikari-Mobiles-
33	DEV CHANDAN IPAD #TBC-Mobiles-
34	HHOTEL FRONT OFFICE IPAD-Mobiles-
35	CDU Scott Davis #TBC-Mobiles-
36	Textum Rob Bykowski iPad-Mobiles-
37	CDU Luke Ronke ipad # TBC-Mobiles-
38	Const RBBIP Pkg 1 - iPad-Mobiles-
APLODATA

Cell Formulas
Range	Formula
E7:E38	E7	=IF(ROWS(E7:E$99)>$E$1,"",INDEX(UNIQUE(FILTER(TRIM('Detail_report (9)'!$S$2:$S$19000),'Detail_report (9)'!$T$2:$T$19000=APLODATA!$E$3)),ROWS(APLODATA!E7:E$99)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:E267	Cell Value	duplicates	text	NO

 

[kvsrinivasamurthy]

It is not clear. May be this in E7.

=unique(filter(TRIM('Detail_report (9)'!$S$2:$S$19000),TRIM('Detail_report (9)'!$S$2:$S$19000)<>""))

 

[ChetanPuri]

It is not clear. May be this in E7.

=unique(filter(TRIM('Detail_report (9)'!$S$2:$S$19000),TRIM('Detail_report (9)'!$S$2:$S$19000)<>""))

Thank you it did work much appreciated

 

[kvsrinivasamurthy]

Welcome