Excel ADO Select Distinct from2 column with numeric value

[SevenDP]

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

 

[Kyle123]

Why can't you use a pivot table?