• 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.

Solve this text retrieve query

S

senthilkumar_rm

Member
I have data as


C5 = text1

D6 = text2

E7 = Text3

F8 = text4


i need the output as


G5 = Text1 (= C5)

G6 = text2 (= D6)

G7 = text3 (= E7)

G8 = text4 (= F8)


I have tried what i know, please help me in solving this
 
Hi Senthil..


I hope you want technique to fetch data in mixture of absolute & relative..

You manage it by lots of techniques.. I would like to prefer OFFSET here..


Can you please try the below for me.. :)


In G5, enter formula as
Code: 
=OFFSET($C$5,COLUMN(A$1)-1,COLUMN(A$1)-1)

now drag toward right..


Regards,

Deb
 
Hi Narayan,


Thank GOD .. you are here..

Thanks for the catch..


BTW.. I always have a backup excuse.. for all my mistakes.. he he he..

now drag toward right..
Click to expand...

Regards,

Deb
 
@Debraj Roy

Hi!

I told you that you should stop posting while still in bed... or at least turn your notebook either counter or simple clockwise 90º. Maybe doing so you avoid appearing transposed.

:)

Regards!
 
Hi Pablo Abad Vázquez,


Just want to inform you that, Please have a look, of my My system. . Its not possible to turn it any side.. as it was fully loaded.


I will send you URL to download the data available in my system. Be prepared with the glass.


Regards,

Deb
 
Last edited:
In G5:

=INDEX($C$5:$F$8,ROWS($A$1:A1),ROWS($A$1:A1))

and copy down.


Now we know the reason why Deb is always in "bubbly" mood.
 
@ shri


ROWS($A$1:A1),ROWS($A$1:A1) should be ROWS($G$5:G5),ROWS($G$5:G5))


otherwise if I delete Col A, it gives you REF error, if I insert a row between row 2 and 5, the results will be incorrect.


Kris
 
@Debraj Roy

Hi!

I fell in love with your system... and people talk about i7-multicores and so on... That monocore is perfect... if we load it with Carlsberg, of course!

Regards!

PS: I'm really in love with it :)

PS2: And you shouldn't try to bring it to your bed, just move your bed aside it!
 
Thanks for formula solution ... but issue is little different I have data as


C5 = text1

D6 = text2

E7 = Text3

F8 = text4

c9 = text 5

D10 = text 6

E11 = text 7

F12 = text 8


i need the output as


G5 = Text1 (= C5)

G6 = text2 (= D6)

G7 = text3 (= E7)

G8 = text4 (= F8)

G9 = text5 (= C9)

G10 = text6 (=D10)

G11 = text7 (= E11)

G12 = text8 (= F12)


It is a long data below on ... but note any one text is availalie in any of 4 columns C,D,E,F and need to fetch those in G column


Formula entered by all provides results to first 4 and returns value 0 for all, pleasehelp
 
It is based on your information:

- Only one column out of the 4 columns will have entry

- It will be string / text entry.


Now the LOOKUP formula doesn't necessarily look for exact string match (as it uses binary search which is similar to using 1 or TRUE as last argument in VLOOKUP/HLOOKUP/MATCH functions) and it returns if it finds a character which is less than what it is searching for.


The last letter we have is "z" so in principle, LOOKUP will return any word beginning with letters 'a' to 'y' and only letter "z" but it will fail as soon as we have a word like "zap" and give #N/A error so we pad it up with one more z to make it "zz". So we have covered nearly all strings that we can come across.


I hope this explains my idea. Please let us know if you need better / more explanation.
 
Good day Debraj Roy


Could you please send the suppliers or Argentinian agents for your system, this is some thing SirJB7 has always dreamed of (so long as it is full of Carlsberg) :)
 
@Debraj Roy

Hi!

Did you read what b(ut)ob(ut)hc wrote? I'm eager to read, I've a pen in my hand a sheet (of paper, not Excel) in front of me.

Regards!
 
Good morning Debraj Roy


Your idea of a Motherboard and a Christmas tree are with out doubt first class and with out equal, SirJB7 will be amazed at your gift..........but can I have one :)
 
@Debraj Roy

Hi!

For I=1 to NumberOfCans

Print ":" & String$(I, ")")

Next I

You actually do know how to move me and make me happy. A couple of tears are streaming down my cheeks...

Regards!


@nazmul_muneer

Hi!

If I didn't read wrongly Debraj Roy clearly stated "... for SirJB7". So don't even dare to think about it.

Regards!


@b(ut)ob(ut)hc

Hi!

Good afternoon, old dog!

Always welcome, come here and have one(*) with me.

Regards!

(*)... one from the six-pack you'd be bringing with you... remember it isn't Christmas yet... so Debraj Roy's gift should and will be kept intact... even if I had to take and accelerated mental control course today.
 
Good evening SirJB7


Hello old friend


Unfortunately the asbestos has got a wee bit worse and I now need a machine four times a day to help me breath so I do not think I will be able to travel for some time but enjoy Debraj Roys most welcome gift and I will join you from here in a few from a six pack :)
 
@b(ut)ob(ut)hc

Hi!

Very sorry to read that, it's because of your winter season or just an illness evolution? Hope and wish that it's the first one.

In the meanwhile have -but only one!- a Carlsberg with me. To you, my friend.

Best regards!


@Debraj Roy

Hi!

I think that b(ut)ob(ut)hc's application should be considered, his doctors say that it might help his defenses increase, if we don't consider the spiritual part.

Regards!
 
Good evening old friend


It does not matter whether it is winter season or illness evolution as always you put a smile on my face
 
You must log in or register to reply here.
Back
Top