• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting Data and Represent the data in a Cell

MSO_user

New Member
Dear Friends

Assalamaualiqum.....Brothers

I Pray for your Good Health and Safety....Brothers

As the title says I have to extract data from a Table and represent the data in a Cells.
Please see the image(img-01) below showing Sheet1.

83682

I have prepared a plan to do it in Logic Sheet, but I am unable to produce the results.
Please see the image(img-02) below showing Logic Sheet.

83683


Please brothers help me in this regards.


Much Thanks Brothers
 

Attachments

  • Extracting Data and represent the data in a Cell.xlsx
    12.3 KB · Views: 5
Please be noted the forum rule " one post one question"

For your question 1, this formula suit with your Excel2013 and all old Excel version.

In A4 formula copied down:

=SUBSTITUTE(TRIM(IF(C4<>""," "&C4&C$3,"")&IF(D4<>""," "&D4&D$3,"")&IF(E4<>""," "&E4&E$3,"")&IF(F4<>""," "&F4&F$3,"")&IF(G4<>""," "&G4&G$3,"")&IF(H4<>""," "&H4&H$3,"")&IF(I4<>""," "&I4&I$3,"")&IF(J4<>""," "&J4&J$3,"")&IF(K4<>""," "&K4&K$3,"")&IF(L4<>""," "&L4&L$3,"")&IF(M4<>""," "&M4&M$3,"")&IF(N4<>""," "&N4&N$3,""))," ","+")

83686
 
Dear bosco_yip Brother

First of all.....Please accept my apologies...Just to explain myself.....
....I prepared the Logic sheet for my understanding and getting the desired results...Sorry if this has causes any confusion to the Forum Members.....

Brother.....I have no words to express my thanks and best wishes for you and your family.....

Please tell me if I can do any thing to support you and this excel forum.

Much Thanks Brothers
 
This is for Excel 365 and 2021 only.
Code:
= BYROW(input, LAMBDA(value,
    TEXTJOIN("+",, FILTER(value&header, value<>"", "") )
  ))
83706
 

Attachments

  • Extracting Data and represent the data in a Cell.xlsx
    13.2 KB · Views: 3
Hi Bosco. I hope things are going OK for you. Thank you for the correction regarding Excel 2021. I had thought that it was only the latest batch of array shaping functions such as TOCOL that was missing, wrongly it seems!

I can't conceive of coping without LAMBDA, it underpins just about everything I do with Excel. Even writing a simple formula I would pause to think whether it would be better presented as a LAMBDA in order to state the intent of the formula whist concealing the computational detail.
Mind you I was probably the only person on this planet that learnt to commit every formula using CSE prior to the 2019 changes; all because I hated the concept of relative referencing. Both in mathematics and natural language I am used to statements that do not change meaning according to location at which they are written on the sheet.

In this case, the lack of BYROW would force me to relative referencing (or setting up an absolute reference for every row) so I defined 'input' to be
=Sheet1!$C4:$N4
and the formula
Code:
{= TEXTJOIN("+",, FILTER(input&header, input<>"","") )}
worked (the CSE being irrelevant). Not that I have any way of testing it on 2021 though.
 
@Peter Bartholomew

Here is my Excel2021 formula testing result:

Formula 1: {=TEXTJOIN("+",,FILTER(header,input<>"",""))} ----> Failed

Formula 2 : =TEXTJOIN("+",,FILTER(header,C4:N4<>"","")) ----> Correct

Formula 3 : =TEXTJOIN("+",,IF(C4:N4<>"",header,""))----> Correct (That is why I used this formula)

83709
 
@bosco_yip
This is the file. I cannot be certain that it will run under 2021. The CSE was applied as a single cell formula with a view to preventing the term
input&header
from using implicit intersection, which was something else I used to dislike. Placing formulas within defined names always used to work better.
As you can see, I was never a 'proper' spreadsheet enthusiast.
 

Attachments

  • Extracting Data and represent the data in a Cell.xlsx
    14.9 KB · Views: 2
Back
Top