[FIXED] How tranpose the data of more than 1 cell to only 1 cell on google sheets?

Issue

I have the following column :

A1
Miu Kei Yuk
3354
of
2018
8 April 2022

and need to separate it into 3 columns : 3354 of 2018 Miu Kei Yuk 8 April 2022

So the first and the fifth row are correct, but the row number 2, 3 and 4 needs to be in only 1 cell.

I tried transpose(A1:A5) but iu results in :

Miu Kei Yuk domingo, março 07, 1909 of  2018    8 April 2022

Is it possible to do it ?

Solution

try:

={A2&" "&A3&" "&A4, A1, A5}

or:

={A2&" "&A3&" "&A4\ A1\ A5}

Answered By - player0

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More

[FIXED] How can I drag down to multiple rows a formula which has more than 1 row in its result?

Issue

I have an extension I am getting the data from, and I am referring to that extension in a formula with result of pre determined rows but I want more than 1 row in its result and that's when I have this problem, because I want to drag the formula to multiple rows but they overlap each other, for example if I wanted 3 rows in the result of the formula starting in row 1 and then drag it down from row 1 to row 3 the formula in row 1 and 2 will show an error because they're overlapped in each other I will put a picture in how it looks...

Is there a way to specify amount of rows as a space between each formula in a way that when I drag the formula down to more rows it will adjust to the "space" I specified?

This is the formula I am using, I am also referring to another sheet as you can see so it'll be great if you can use this formula to answer my question, if I can specify the "space" using another formula that is (also it's probably obvious but the pre determined rows in the formula is the "2d").

=CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d")

Solution

this is usually solved by constructing an array of formulae where you stack them up in the line like:

={CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C5&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C6&"/USD", "price_history", "2d")}

this way the 2nd fx will pick up right after 1 fx ends

you can ease your pain of a "hand job" from constructing such an array - especially if that array needs to span over the larger range - by building a formula to generate a formula. for example: https://stackoverflow.com/a/68278101/5632629

also, make sure you obey the law of array constructs and successfully avoid all array errors - https://stackoverflow.com/a/58042211/5632629

Answered By - player0

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How to Flip a string in google sheets

Issue

I have this google sheets input column on the left, I want to flip the string like shown in the output column.

Input   Output
--------------
bats    stab
live    evil
meet    teem
part    trap
stop    pots

Solution

Split the string to it's characters using regex delimiter, then REDUCE the string using current&accumulator(reversal happens here):

=REDUCE(,SPLIT(REGEXREPLACE(A2,,"🐾"),"🐾"),LAMBDA(a,c,c&a))

For a array, use BYROW:

=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(str,REDUCE(,SPLIT(REGEXREPLACE(str,,"🐾"),"🐾"),LAMBDA(a,c,c&a))))

Answered By - TheMaster

Answer Checked By - Timothy Miller (PHPFixing Admin)

Read More

[FIXED] How to get specific values of items from other sheets based on column names in the master sheet?

Issue

To describe my problem here is my sheet

Master sheet

Here, From Column E onwards there are different attributes for each of the items in column A.

Now, I have a subsheet based on the type of the items as listed in column B, here

Subsheet

Here we can see from Column C onwards attributes of the items in column A. In this case, the other attributes from the master sheet above that are not mentioned do not apply to these items and therefore will be blank in the master sheet.

My problem is to populate the attributes of the corresponding items from the subsheet that are in column C to F, in the master sheet where all the attributes are listed. I only want to add those values that exist in the sub sheet and leave the rest blank.

Here is a sample sheet with the desired output. There is a Master sheet and an Subsheet named DEF. The Master sheet has to lookup the values from the Subsheet and filled the appropriate cells based on the reference from column A and B of the Master sheet.

https://docs.google.com/spreadsheets/d/1lNPPhTr7_N0N8CA7BLyO9JhYGd1Hx0c6Q0Uq4Wdt_GY/edit?usp=sharing

Solution

try in D2:

=INDEX(IF(A2:A="",,IFERROR(VLOOKUP(A2:A&B2:B, {DEF!A:A&DEF!B:B, DEF!C:Z}, 
 IFERROR(MATCH(D1:P1, DEF!A1:Z1, 0)-1, 100), 0))))

Answered By - player0

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More