Group by and Transpose in Query?

Hi all,

I'm not really familiar with the use of Queries in Excel 2016 but my attempts to get the required result by Merge and or Transpose did not succeed.

This is data in the source_table

T1.jpg

What I need is a new table with
- 1 row per CODE (1st column)
- 1 column per CI_LOC with data from My Cust No per CODE

The result should look like this:

T2.jpg

I know how to get this done by using a Pivot but would like to create a new Table instead.

Can anybody tell me what I should do?

* attached is the data in excelfile

is that what you want?

btw your example result is incorrect because there are different groups, diff. ci_loc & diff. cust_num

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)

If you did it already - ignore it.
Thank you.

Hi sandy,

Oops, I made a mistake by marking my question as solved and adjusted my response just now.

I'm trying "Group By" now. Is that the way to go?

If you edit query table you will see steps

qs.jpg

Hi sandy,

I've figured it out, had to adjust the table for item 25 /57.
Now all items are on 1 line.

I have lots to learn about queries, but it's a great tool.

Thanks!

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)

If you did it already - ignore it.
Thank you.