Repositioning data in columns

Question

A table has a list of items in column B. Each item has a corresponding value of either a, b or c in column M, corresponding value of either d, e, or f in column N and a corresponding value g in column O.

Would like to create another table were each item appears in column A and its corresponding value of either a, b or c appears in columns B, C and D, respectively; its corresponding value of d, e or f appears in columns E, F and G, respectively; and its value g appears in column H.  

Any suggestions how this can be done using formulas instead of Power Query

Thank you

Answer 1

Hi,

(the formula only works for Excel 365).

Look at the example I have given in the screenshot and in cell A15 and apply the following formula. This formula regroups all unique data according to the letters located in cells B15:H15.

=LET(ts, TEXTSPLIT(TEXTJOIN(";",, TOCOL(B2:B10 & "," & IF(M2:O10 = "", NA(), M2:O10), 3)), ",", ";"), tk, TAKE(ts,, -1), PIVOTBY(TAKE(ts,, 1), tk, tk, SINGLE,, 0,, 0))

If you want to see all the data grouped according to the letters of cells B15:H15, then in the formula instead of the SINGLE function, put the ARRAYTOTEXT function.

While in case you want to have the amount of each grouping based on the letters of cells B15:H15, then in the formula instead of the SINGLE function, put the COUNTA function.

Hope this helps.

IlirU

Answer 2

Hi,

In cell B13, enter this formula

=LET(t,HSTACK(DROP(REDUCE("",SEQUENCE(ROWS(B2:B10)),LAMBDA(s,c,VSTACK(s,IF(SEQUENCE(COLUMNS(INDEX(M2:O10,c,))),INDEX(B2:B10,c,1))))),1),TOCOL(M2:O10)),f,FILTER(t,CHOOSECOLS(t,2)<>""),PIVOTBY(CHOOSECOLS(f,1),CHOOSECOLS(f,2),CHOOSECOLS(f,2),SINGLE,,0,,0))

Hope this helps.

Answer 3

To achieve the desired table layout using formulas in Excel, you can use a combination of the INDEX and MATCH functions. Here’s a step-by-step approach:

1. Set Up Your New Table: Create a new table where you want to display the data. Let's assume you start this new table in cell A1.
2. List Items in Column A: In cell A2 of your new table, enter the formula to list the items from the original table. If your original table starts from B2, you can use:

      =UNIQUE(B2:B100)
   

   This will list unique items from column B.
3. Retrieve Values for Columns B, C, D: For the corresponding values a, b, or c from column M: In cell B2, use:

      =IFERROR(INDEX($M$2:$M$100, MATCH(A2, $B$2:$B$100, 0)), "")
   

   Drag this formula across to C2 and D2 to fill in the corresponding values for each item.
4. Retrieve Values for Columns E, F, G: For the corresponding values d, e, or f from column N: In cell E2, use:

      =IFERROR(INDEX($N$2:$N$100, MATCH(A2, $B$2:$B$100, 0)), "")
   

   Similarly, drag this formula across to F2 and G2.
5. Retrieve Value for Column H: For the value g from column O: In cell H2, use:

      =IFERROR(INDEX($O$2:$O$100, MATCH(A2, $B$2:$B$100, 0)), "")
   

6. Finalize the Table: Drag down the formulas in columns A through H to fill in the rest of the table for all items.

This approach will dynamically pull the corresponding values based on the items listed in your new table, ensuring that each item appears with its respective values in the desired format.