Excel ADO Select Distinct from2 column with numeric value

SevenDP

New Member
Joined
Mar 10, 2016
Messages
21
Hi guys

Please help...
I have data:

District; Type; Price1; Price2
JKT; Type1; 100;
JKT; Type1; 101;
JKT; Type1; 104;
JKT; Type1; ; 111
JKT; Type1; ; 22
JKT; Type2; 50;
JKT; Type2; ; 150
JKT; Type2; ; 15
SMG; Type2; ; 44
SMG; Type2; ; 144

expected result:
Same District & Type, then Price1 &Price2 will become 1 row
if there are another same district & type, it will create new row

District; Type; Price1; Price2
JKT; Type1; 100; 111
JKT; Type1; 101; 22
JKT; Type1; 104;
JKT; Type2; 50; 150
JKT; Type2; ; 15
SMG; Type2; ; 44
SMG; Type2; ; 144

can achieve it using ADO SQL ?

sql = "SELECT DISTINCT [District], [Type], [Price1], '' FROM [Hasil$A4:D216] WHERE [Price1] <> 0 ORDER BY [District], [Type] UNION ALL"
sql = sql & " SELECT DISTINCT [District], [Type], '', [Price2] FROM [Hasil$A4:D216] WHERE [Price2] <> 0 ORDER BY [District], [Type]"
but can not group it (GROUP BY [District], [Type] got error)

or there are other ways to do that ?
many thanks guys
 
Last edited:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,099,164
Messages
5,467,003
Members
406,516
Latest member
richcresswell

This Week's Hot Topics

Top