Transpose Data with Criterion

[deciog]

Good Morning.

I didn't find a way to do this solution according to the attached spreadsheet

I need it in non-VBA non Power Bi formulas non table.

It can be Excel version 2016 or 365

thank you in advance

 

[p45cal]

Not sure if this is quite right, it sorts the names alphabetically and maybe that's not what you want.
Office 365 (no special insider version) formula in cell K3 (see attached):

=LET(e,A3:D17,d,INDEX(e,MOD(SEQUENCE(ROWS(e)*2)-1,ROWS(e))+1,INT((SEQUENCE(ROWS(e)*2)-1)/ROWS(e))*2+1),b,SORT(UNIQUE(FILTER(d,d<>0))),a,VLOOKUP(b,e,2,0),c,VLOOKUP(b,OFFSET(e,0,2,,2),2,0),CHOOSE({1,2,3,4},IF(ISERROR(a),"",b),IF(ISERROR(a),"",a),IF(ISERROR(c),"",b),IF(ISERROR(c),"",c)))

I'm sure it could be more elegant. Just what first worked as I developed it.
For Excel 2016 I wouldn't like to develop a formula, but Power Query is available in Excel 2016, but would this be breaching the 'no table' requirement? If not I'll have a go.

 

[John Jairo V]

Hi to both!

Another catch with Microsoft 365 Insider version:

=LET(_r,VSTACK(A3:A17,C3:C17),
_s,SORT(UNIQUE(FILTER(_r,_r<>""))),
_a,VLOOKUP(_s,A3:B17,2,),
_b,VLOOKUP(_s,C3:D17,2,),
VSTACK(A2:D2,HSTACK(IF(ISERROR(_a),"",IF({1,0},_s,_a)),IF(ISERROR(_b),"",IF({1,0},_s,_b)))))

Blessings!

 

[John Jairo V]

Hi again!

Second try... (Microsoft 365 Insider version too):

=LET(_r,VSTACK(A3:A17,C3:C17),
_s,SORT(UNIQUE(FILTER(_r,_r<>""))),
_a,INDEX(A3:B17,XMATCH(_s,A3:A17),{1,2}),
_b,INDEX(C3:D17,XMATCH(_s,C3:C17),{1,2}),
VSTACK(A2:D2,IFERROR(HSTACK(_a,_b),"")))

Blessings!

 

[deciog]

p45cal

It worked perfectly for Excel 365 version thanks

I also need for Excel 2016 version I have more than 20 sheets with this version


John Jairo V

In my version of Excel 365 it doesn't have this VSTACK function I'll wait for a soft update but I saved this formula, Thank you

I also need for Excel 2016 version I have more than 20 sheets with this version

Decio

 

[p45cal]

Again,

For Excel 2016 I wouldn't like to develop a formula, but Power Query is available in Excel 2016, but would this be breaching the 'no table' requirement? If not I'll have a go.

 

[deciog]

John Jairo V

I ask you to attach the model with the formula, so Excel itself will convert the formula.

Example of formula in English VLOOKUP and in Portuguese Brazil automatically translated by Excel is PROCV

So I believe that if you put the model Excel will automatically convert the VSTACK Function to Portuguese.

On the website "https://en.excel-translator.de/translator/" there is still no translation

thanks in advance

Decio

 

[Marc L]

Hello, no need to translate anything as the Excel formulas engine is natively in english whatever the local version !​

​

Tip : how to enter a native (english) Excel formula in a local version ?​

​

• Select the formula destination cell in a worksheet.​

• Open VBE (Alt F11).​

• Open Immediate window (Ctrl G).​

• Type the formula in this window : activecell.formula="formula" & validate.​

​

Tip #2 : if the native formula contains double quotes so each one must be doubled when typing the formula …​

Do you like it ? So thanks to click on bottom right Like !​

​

 

[bosco_yip]

Here is a formula way with 2 helpers column can work in your Excel 2016

1] Helper 1 (Combine 2D >1D + unique list) In P3, array (CSE) formula copied down :
=IFERROR(INDIRECT(TEXT(MIN(IF((COUNTIF(P$2: P2,A$3:C$17)=0)*(ISTEXT(A$3:C$17)),ROW($3:$17)*1000+{1,2,3})),"r0c000"),),"")

2] Helper 2 (Sort unique list)
=IFERROR(INDEX("Name-"&TEXT(BASE(SMALL(DECIMAL(SUBSTITUTE(P$3:INDEX(P: P,MATCH(2,1/(P$1: P$50<>""))),"Name-",""),36),ROW($1:$30)),36),"[>]0;"),ROW(A1)),"")

3] "Name" in K3, formula copied :
=IF(ISNUMBER(MATCH($Q3,A$3:A$17,0)),$Q3,"")

4] "Value" in L3, formula copied :
=IFERROR(VLOOKUP(K3,A$3:B$17,2,0),"")

And,
5] Copied K3 formula to M3 and L3 formula to N3, then all copied down

 

[deciog]

Bosco, good morning.

It worked perfectly, thanks for the help

Hugs

Decio