Apr 18 2020 02:25 AM
I am running Excel 365 (1908), have a csv from a dB and need to massage data. Comes in with up to 8 rows of data per user. Need to convert to table and place data in correct column depending on Question
User | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8
1000 | A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8
=====<sample csv>====Actual data 3000 rows
"User","Question","Answer"
1000,Q1,A1
1000,Q8,A8
1000,Q3,A3
1001,Q4,A4
1001,Q3,A1
1002,Q2,A2
1002,Q3,A3
Figured would need to sort data by User and then Question. If I can build a macro to do this that would be helpful since the data will need to be updated weekly.
This is to be able to generate a report for our team of volunteers.
Thanks, Deb
Apr 18 2020 02:51 AM
That could be done by Power Query - query csv file or directly database, pivot on questions and return table back with some cosmetic
Script is like
let
Source = Csv.Document(File.Contents("C:\Test\users.csv"),
[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
PromotHeaders = Table.PromoteHeaders(
Source, [PromoteAllScalars=true]
),
SortByQuestion = Table.Sort(
PromotHeaders,
{{"Question", Order.Ascending}}
),
PivotOnQuestion = Table.Pivot(
SortByQuestion,
List.Distinct(SortByQuestion[Question]),
"Question", "Answer"
),
SortByUser = Table.Sort(
PivotOnQuestion,
{{"User", Order.Ascending}}
)
in
SortByUser