Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
I have a list of products that have different options, that will be displayed
on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Date< SHOULD READ DATA
SORRY! "Excel Helps" wrote: I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Hi
Try =A2&IF(A2<"","¦","")&A2&IF(A2<"","|","")&B2&IF( B2<"","¦","") &B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF( C2<"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Thanks Roger,
I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","¦","")&A2&IF(A2<"","|","")&B2&IF( B2<"","¦","") &B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF( C2<"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
To save some typing you could try this UDF, which ignores blank cells and adds
the pipe only when there is data. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|" Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:C10) Gord Dibben MS Excel MVP On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps wrote: Thanks Roger, I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","¦","")&A2&IF(A2<"","|","")&B2&IF(B 2<"","¦","") &B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF(C 2<"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Hi Gord
I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... To save some typing you could try this UDF, which ignores blank cells and adds the pipe only when there is data. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|" Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:C10) Gord Dibben MS Excel MVP On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps wrote: Thanks Roger, I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","¦","")&A2&IF(A2<"","|","")&B2&IF(B 2<"","¦","") &B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF(C 2<"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Thank you both for your very welcome advice.
I have just re-sent my query as the broken pipes were missing from the last couple of posts. The actual product file that I have has 8 columns of choices, per product that will display as a dropdown menu. If this can be done by a UDF it would save a long keyboard session, I am new to UDF'S although I have created the CSV output file from my product list using a macro so I guess I could manage it with a little help. In the meantime I will try Roger's method. Thank you both, from a keen novice! RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" "Roger Govier" wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text redred|whitewhite|blueblue| -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... To save some typing you could try this UDF, which ignores blank cells and adds the pipe only when there is data. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|" Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:C10) Gord Dibben MS Excel MVP On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps wrote: Thanks Roger, I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","","")&A2&IF(A2<"","|","")&B2&IF(B2 <"","","") &B2&IF(B2<"","|","")&C2&IF(C2<"","","")&C2&IF(C2 <"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white redred|whitewhite|blueblue| redred||blueblue| |whitewhite|| ||| The function to achieve the seperated pipes is: =A2&""&A2&"|"&B2&""&B2&"|"&C2&""&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Hi
Modifying Gord's code as follows, should do what you want. I have included the final solid pipe after the last cell value. If you don't want it, then use Gord's final line. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String Dim pipe As Boolean pipe = True For Each Cell In CellBlock pipe = Not pipe If Len(Cell.Text) 0 Then If pipe = False Then sbuf = sbuf & Cell.Text & Chr(166) Else sbuf = sbuf & Cell.Text & "|" End If End If Next ConCatRange = sbuf End Function Press Alt+F11 to enter the VB Editor. If you have a module within the workbook, copy and paste the function there. If not, choose InsertModule then paste the code into the large white pane of the new module. The code will work with any range of cells. If you are wanting 8 columns on a single line then on the spreadsheet type =concatrange(A1:H1) and copy down -- Regards Roger Govier "Excel Helps" wrote in message ... Thank you both for your very welcome advice. I have just re-sent my query as the broken pipes were missing from the last couple of posts. The actual product file that I have has 8 columns of choices, per product that will display as a dropdown menu. If this can be done by a UDF it would save a long keyboard session, I am new to UDF'S although I have created the CSV output file from my product list using a macro so I guess I could manage it with a little help. In the meantime I will try Roger's method. Thank you both, from a keen novice! RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" "Roger Govier" wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text redred|whitewhite|blueblue| -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... To save some typing you could try this UDF, which ignores blank cells and adds the pipe only when there is data. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|" Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:C10) Gord Dibben MS Excel MVP On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps wrote: Thanks Roger, I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","","")&A2&IF(A2<"","|","")&B2&IF(B2 <"","","") &B2&IF(B2<"","|","")&C2&IF(C2<"","","")&C2&IF(C2 <"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white redred|whitewhite|blueblue| redred||blueblue| |whitewhite|| ||| The function to achieve the seperated pipes is: =A2&""&A2&"|"&B2&""&B2&"|"&C2&""&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Thanks Roger.
These old eyes did not pick up the difference in the piping. Gord On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Thank you
I will work on this in the morning. I'll send a post just to let you both know how I get on. Thanks again. "Roger Govier" wrote: Hi Modifying Gord's code as follows, should do what you want. I have included the final solid pipe after the last cell value. If you don't want it, then use Gord's final line. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String Dim pipe As Boolean pipe = True For Each Cell In CellBlock pipe = Not pipe If Len(Cell.Text) 0 Then If pipe = False Then sbuf = sbuf & Cell.Text & Chr(166) Else sbuf = sbuf & Cell.Text & "|" End If End If Next ConCatRange = sbuf End Function Press Alt+F11 to enter the VB Editor. If you have a module within the workbook, copy and paste the function there. If not, choose InsertModule then paste the code into the large white pane of the new module. The code will work with any range of cells. If you are wanting 8 columns on a single line then on the spreadsheet type =concatrange(A1:H1) and copy down -- Regards Roger Govier "Excel Helps" wrote in message ... Thank you both for your very welcome advice. I have just re-sent my query as the broken pipes were missing from the last couple of posts. The actual product file that I have has 8 columns of choices, per product that will display as a dropdown menu. If this can be done by a UDF it would save a long keyboard session, I am new to UDF'S although I have created the CSV output file from my product list using a macro so I guess I could manage it with a little help. In the meantime I will try Roger's method. Thank you both, from a keen novice! RED WHITE BLUE red white blue red blue white red¦red|white¦white|blue¦blue| red¦red|¦|blue¦blue| ¦|white¦white|¦| ¦|¦|¦| The function to achieve the seperated pipes is: =A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|" "Roger Govier" wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text redred|whitewhite|blueblue| -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... To save some typing you could try this UDF, which ignores blank cells and adds the pipe only when there is data. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|" Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =concatrange(A1:C10) Gord Dibben MS Excel MVP On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps wrote: Thanks Roger, I can now improve my file, I had to concede and put N/A where I really needed a blank cell. Have a good weekend. "Roger Govier" wrote: Hi Try =A2&IF(A2<"","","")&A2&IF(A2<"","|","")&B2&IF(B2 <"","","") &B2&IF(B2<"","|","")&C2&IF(C2<"","","")&C2&IF(C2 <"","|","") -- Regards Roger Govier "Excel Helps" wrote in message ... I have a list of products that have different options, that will be displayed on a website with various dropdown menus. The menus are created from a CSV Import file that is itself created from a large excel file simaler to this example: RED WHITE BLUE red white blue red blue white redred|whitewhite|blueblue| redred||blueblue| |whitewhite|| ||| The function to achieve the seperated pipes is: =A2&""&A2&"|"&B2&""&B2&"|"&C2&""&C2&"|" My question is, what do I need to add to the function to NOT DISPLAY THE PIPES WHEN THE CELL CONTAINS NO DATE. At the moment, with my function, even if the cell contains no data I still have the Pipes displayed which I don't want, I need just empty cells. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
The UDF works fine,but what do I need to use it to pull data from a different
worksheet. For Example rather than =ConCatRange(A2:E2) I would like to reference =sheet9!ConCatRange(A2:E2) "Gord Dibben" wrote: Thanks Roger. These old eyes did not pick up the difference in the piping. Gord On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Is there any way to modify the UDF to display this:
data1¦data1|data2¦data2|data3¦data3 So the data is duplicated but with a broken pipe, then a pipe until the end of the string which has no pipe Also I need to reference a separate worksheet for the data and display in another sheet. Thanks "Excel Helps" wrote: The UDF works fine,but what do I need to use it to pull data from a different worksheet. For Example rather than =ConCatRange(A2:E2) I would like to reference =sheet9!ConCatRange(A2:E2) "Gord Dibben" wrote: Thanks Roger. These old eyes did not pick up the difference in the piping. Gord On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Hi
Try =concatrange(Sheet9!A1:H1) where Sheet 9 contains the data. The formula can be entered on any Sheet. To get rid of the last pipe, then amend the line ConCatRange = sbuf) to ConCatRange = Left(sbuf, Len(sbuf) - 1) -- Regards Roger Govier "Excel Helps" wrote in message ... Is there any way to modify the UDF to display this: data1¦data1|data2¦data2|data3¦data3 So the data is duplicated but with a broken pipe, then a pipe until the end of the string which has no pipe Also I need to reference a separate worksheet for the data and display in another sheet. Thanks "Excel Helps" wrote: The UDF works fine,but what do I need to use it to pull data from a different worksheet. For Example rather than =ConCatRange(A2:E2) I would like to reference =sheet9!ConCatRange(A2:E2) "Gord Dibben" wrote: Thanks Roger. These old eyes did not pick up the difference in the piping. Gord On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed
Hi Roger
I may have been unclear with my first post. What I need to do is if I have 4 columns of data for example (I've actually got up to 8 columns of choices (colour) 8 of (size) 5 of (material) etc per line of product which I need to be output eventually to a CSV File as so: red blue white pink green orange black grey As Headings Some products have all, some none, some have 1 - 8 So the output needs to be: red¦red|black¦black etc etc. The data from each cell in the first sheet is repeated twice in the second sheet with 1broken pipe then 1 pipe separating each entry. Your previous code worked perfectly but the data is only displayed once as this: red¦black|blue¦ The pipes are alternating correctly but the data is only displayed once and not twice. Sorry to be a pain, but the web design programme needs the permutations imported that way. Thank you for your patience! "Roger Govier" wrote: Hi Try =concatrange(Sheet9!A1:H1) where Sheet 9 contains the data. The formula can be entered on any Sheet. To get rid of the last pipe, then amend the line ConCatRange = sbuf) to ConCatRange = Left(sbuf, Len(sbuf) - 1) -- Regards Roger Govier "Excel Helps" wrote in message ... Is there any way to modify the UDF to display this: data1¦data1|data2¦data2|data3¦data3 So the data is duplicated but with a broken pipe, then a pipe until the end of the string which has no pipe Also I need to reference a separate worksheet for the data and display in another sheet. Thanks "Excel Helps" wrote: The UDF works fine,but what do I need to use it to pull data from a different worksheet. For Example rather than =ConCatRange(A2:E2) I would like to reference =sheet9!ConCatRange(A2:E2) "Gord Dibben" wrote: Thanks Roger. These old eyes did not pick up the difference in the piping. Gord On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Gord I don't think that will work. the OP is using two different forms of pipe between the text red¦red|white¦white|blue¦blue| |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Help Needed please | Excel Worksheet Functions | |||
Help Needed Urgent | Excel Discussion (Misc queries) | |||
Urgent Help needed | Excel Discussion (Misc queries) | |||
Urgent help needed | Excel Worksheet Functions | |||
Urgent help needed! | Excel Worksheet Functions |