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

Fill Cell Using Formula

Thanks For Reply
Yes Sir, I Want result in C,D,E,F,G..... Column And A Column is DATA And Column B is CODE, I want result in Column C,D,E,F,G..... combine with CODE And -1 , -2 , -3, -4, -5,... That world present in A column so, fill Column C,D,E,F,G.... accoding to CODE And -1 , -2 , -3, -4, -5,...
 
Nrpatel
I asked expected results in columns C..G? eg in C2?
hmm?
Are You 99% sure, that You would like to get ALL above text to cell C2?
 
Thanks For Reply
Please Refer new Attachment for Better understanding
Thanks Once Again And Sorry for not understanding what u say.
With warm Regards.
 

Attachments

  • New image url.xlsx
    21.7 KB · Views: 4
Nrpatel
I tried to ask:
What should be in those cells in C...G-columns?
Now Your 'sample' results are more wild!
C6 has text Example
C7 has the whole link
D16 has text NOTE
C18 has sentence, some text are red
C19 has sentence, some text are red
C20 has sentence, some text are red

... one thing is sure, that I won't create those arrows!

Is any of Your given 'sample result' as You would like to get?
 
Thanks Once Again And Sorry for not understanding what u say.
I Want whole link in those columns C...G Accoding to Code, Those code present in link.
Please Refer new Attachment Sample
 

Attachments

  • Sample.xlsx
    19 KB · Views: 5
Nrpatel
Finally!
Press [ Do It ]-button
There are two modes for 'filling' ... You'll see 'animated' ... other one is quicker.
Ps. You'll also see which datas cannot 'fill'
 

Attachments

  • Sample.xlsb
    33.9 KB · Views: 5
You can also do this with a formula; in cell C3 array-enter the following formula (array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter):
Code:
=TEXTJOIN(,TRUE,IF(ISNUMBER(FIND("/" & $B3 & C$2 & ".jp",$A$3:$A$287)),$A$3:$A$287,""))
Then you can copy across 5 cells, then copy those down to row 83.

You may want to Copy|Paste-Special, Values in situ afterwards.

You could also do this formula-entering with a macro, although for the moment, this one is hard-coded for your sample sheet:
Code:
Sub blah()
Range("C3").FormulaArray = "=TEXTJOIN(,TRUE,IF(ISNUMBER(FIND(""/"" & RC2 & R2C & "".jp"",R3C1:R287C1)),R3C1:R287C1,""""))"
Range("C3").AutoFill Destination:=Range("C3:G3")
Range("C3:G3").AutoFill Destination:=Range("C3:G83")
Range("C3:G83").Value = Range("C3:G83").Value
End Sub
 
Last edited:
Or, try this simply non-array VLOOKUP formula :

In C3, copied across and down :

=IF($B3="","",IFERROR(VLOOKUP("*"&$B3&C$2&"*",$A$3:$A$287,1,0),""))

Regards
Bosco
 

Attachments

  • CodeExtension(BY).xlsx
    32.9 KB · Views: 10
Back
Top