LinkBack URL
About LinkBacks
I need a formula that if there is a number in cell A3 or B3 or C3 etc or multiple cells, i need the details from A1/B1/C1 etc to show in cell Z1
I need a formula that if there is a number in cell A3 or B3 or C3 etc or multiple cells, i need the details from A1/B1/C1 etc to show in cell Z1
Z1=You can also use spaces between de text.
Please Login or Register to view this content.
z1 =Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi
look at concatenate function. It may help You.
How about..
=IF(COUNT(A3:C3)>0,CONCATENATE(A1,B1,C1),"")
adapt your range accordingly
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks Ace_XL, works great but my range is vast, it runs from G to AS so the end part of the formula, ie G9,H9,I9,J9 etc will be very big, is there a way to cut this down? thanks
I don't get it, you want to CONCATENATE cells G9 til AS9 in one cell, instead of A1 till c1?
You would need a User Defined Function (UDF) for multiple concatenating. See below
http://www.excelforum.com/excel-gene...e-formula.html
i've attached a snippet of the spreadsheet.
what im after is if there is a number in cells G12 to AS12, in cell F12 i want it to read what is in cells G9:AS9. eg if there is a 1 in H12 and a 1 in K12, i want F12 to read what is in H9 and K9
The post that Ace linked you to is your best bet. Alternately, you could download MoreFunc (google it) which is a free add in which contains that same function. The link also tells you how to "install" the code. Once that code is added,
=aconcat(IF(ISNUMBER(G12:AS12),G$9:AS$9,""),", ") entered as an Array (use CNTRL + SHFT + ENTER instead of ENTER)
Does that help?
EDIT: The above formula gives you lots of extra commas, instead use this array
=SUBSTITUTE((ACONCAT(IF(ISNUMBER(G13:AS13),G$9:AS$9,),", ")),"0, ","")
Attaching a file
Last edited by ChemistB; 08-06-2012 at 10:55 AM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
thanks ChemistB, just a query with the formula, in cell S12 is a number an it corresponds to S9 (1800) but in cell F12 it doesn't display it correctly. thanks
Last edited by petelomax; 08-06-2012 at 11:06 AM.
^^^whats that supposed to mean???Basically it means there is no built in way to do this so you need to make one. In Post 7, Ace_XL linked you to some code which creates a User defined Function (UDF) called aconcat. With aconcat, you can use ranges rather than entering single cells like in the regular CONCAT function.
Unfortunately, you don't want the entire range, just the ones where there's a number in a particular row (i.e row 12). That's why my formula has the IF function which pulls out those cells. It still leaves a lot of commas between (one for every cell that doesn't match the IF statement) so the SUBSTITUTE part takes those out. Clear as mud?
ChemistB, can you look again at the spreadsheet please, and post #10. thanks
hi can anybody help with this please. what chemistB has done is nearly right, it just isnt giving all of the correct figures
Try this one instead, this still needs to be Array confirmed with Shift Ctrl Enter
=SUBSTITUTE(ACONCAT(IF(G12:AS12>0,$G$9:$AS$9&", ",""))&"|",", |","")
what do you mean by "Array confirmed with shift ctrl Enter"?
Copy the formula and paste it into your sheet, then press Shift Ctrl & Enter together, the same way you would press Ctrl Alt & Delete to open task manager.
It's easy to tell if it's worked, if you do it right you get the correct result, if you do it wrong you get #N/A!
hi its coming up with a #name? error
Which of the custom functions did you add from the earlier suggestions, ACONCAT, or morefunc?
It is meant:
instead of confirming the forumula with an enter, you need to confirm this formula with CTRL SHIFT ENTER (or SHIFT CTRL ENTER)
jason.b75 - i think it will be easier to start again with me re-attaching the spreadsheet again and going from there. So ok ive re-attached the spreadsheet. i need the formula in cell h12 (and below) that if there is a number in I12:AV12, i need the description from I9:AV9 in cell H12, with commas between each description. thanks
You didn't add the code for the UDF to your workbook, as chemistb suggested in the first line of post #9, if you followed his / her other suggestion and installed morefunc then you don't need the code, but you will need to use the MCONCAT formula, not the ACONCAT formula.
Also there were some minor errors in the formula that both chemistb and myself suggested, this one based on chemistb's formula works properly.Please Login or Register to view this content.
With the code above use
=SUBSTITUTE(ACONCAT(IF(ISNUMBER(I12:AV12),I$9:AV$9,),", ")&", ","0, ","")
Array confirmed with Shift Ctrl Enter
Or with the morefunc addin installed use
=SUBSTITUTE(MCONCAT(IF(ISNUMBER(I12:AV12),I$9:AV$9,),", ")&", ","0, ","")
Array confirmed with Shift Ctrl Enter
i copied the code into VBA. and put the aconcat formula in cell H12 and shift ctrl enter. it works but a slight error, it should read "1800, 1ft" but instead it reads "1801ft"
I didn't see that coming
The formula returned "0, " for each empty cell in row 12, then SUBSTITUTE() removes all occurrences of "0, " from the string, this included removing the middle part of "1800, 1ft" which met the criteria.
Fixing that seemed to break the formula elsewhere, starting again, I tried
=IFERROR(SUBSTITUTE(ACONCAT(IF(ISNUMBER(I12:AV12),TRIM(I$9:AV$9)&", ","")),",","",COUNT(I12:AV12)),"")
which seems to work. As before, Array confirm with Shift Ctrl Enter.
absolutely spot on thanks jason.b75!!!
didn't see second page, couldn't figure out how to delete post
The simple elegant solution always comes after you post the not so simple ugly version.
=SUBSTITUTE(ACONCAT(IF(ISNUMBER(I12:AV12),", "&TRIM(I$9:AV$9),"")),", ","",1)
The TRIM() function wouldn't normally be needed but I noticed that some of your text descriptions had some stray spaces in so this is used to remove them.
To simplify it even more, in another thread with a similar problem, this was suggested,
Code written by stunn
Formula,Please Login or Register to view this content.
=concatheaders(I$9:AV$9,I12:AV12,", ")
Which doen't need to be array confirmed.
Last edited by jason.b75; 08-10-2012 at 09:52 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks