Change some Rows in excel to Column

Question

I have a CSV file in the format like below,

I need to import the data from the csv into Salesforce DB.It has to insert records in to 4 different tables that are related

Card 
  -->Section       
    --> Question 
       --> Answer

I need to normalize the table like instead of having the Multiple answers appearing in the same row I need to have each answers in its own row like

It is not necessarily I have only two answers in a row, it can even have 4 or 5 answers. If that is the case I need to add one row for each answers. Is this something doable through excel formulas. Any help is greatly appreciated

score 2 · Answer 1 · answered Mar 10 '23 at 04:43

Unpivot (Excel Formula)

=LET(Data,A2:G6,rCols,3,
    rData,TAKE(Data,,rCols),vData,TAKE(Data,,rCols-COLUMNS(Data)),
    cSeq,SEQUENCE(,rCols),
DROP(REDUCE("",SEQUENCE(ROWS(rData)),LAMBDA(rRes,rRow,
    LET(vrData,INDEX(vData,rRow),vfData,TOCOL(FILTER(vrData,vrData<>"")),
VSTACK(rRes,HSTACK(INDEX(rData,SEQUENCE(ROWS(vfData),,rRow,0),cSeq),vfData))))),1))

score 1 · Accepted Answer · answered Mar 10 '23 at 10:17

Simplest way to do this is using Power Query, hold down the first three columns and right click unpivot other columns.

it even works when the number of columns increased.

Change the data in a table and open a blank query from data tab. When the Power Query editor opens add the following in the advance editor hit ok, close and load to a new sheet or existing sheet as you prefer.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Card Name", type text}, {"Sec Name", type text}, {"Question Text", type text}, {"Answer 1", type text}, {"Answer 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Card Name", "Sec Name", "Question Text"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Answer"}})
in
    #"Renamed Columns"

I tried the suggestion hear and it help.. Can you please help with this https://stackoverflow.com/questions/75716657/changing-some-rows-of-the-csv-file-in-to-column-for-data-load — user4912134, Mar 12 '23 at 22:10

David Leal · Answer 3 · 2023-03-11T14:37:00.413

You can try this for the case of two answers, for more replace 2 in the formula accordingly and adjust the answer range.

=HSTACK(CHOOSEROWS(A2:C5, INT(SEQUENCE(2*ROWS(A2:C5),,0)/2+1)),TOCOL(D2:E5))

If you have a variable number of answers. For example, some row with 4 answers and some other row with 5. Expand the answer range to its maximum, let's say n and then filter the result by removing rows with empty answers.

For example, here the case of n=2, but removing empty answers:

=LET(n, 2, in, A2:C5, ans, TOCOL(D2:E5), ansCol, COLUMNS(in)+1,
 out, HSTACK(CHOOSEROWS(in, INT(SEQUENCE(n*ROWS(in),,0)/n+1)),ans),
 FILTER(out, INDEX(out,, ansCol)<>0))

Change some Rows in excel to Column

3 Answers3

Unpivot (Excel Formula)