Urgent help needed

[Excel Helps]

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.

 

[Excel Helps]

Date< SHOULD READ DATA
SORRY!

 

[Roger Govier]

Hi

Try
=A2&IF(A2<>"","¦","")&A2&IF(A2<>"","|","")&B2&IF(B2<>"","¦","")
&B2&IF(B2<>"","|","")&C2&IF(C2<>"","¦","")&C2&IF(C2<>"","|","")

 

[Excel Helps]

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.

 

[Gord Dibben]

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

 

[Roger Govier]

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|

 

[Excel Helps]

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]

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 Insert>Module> 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

 

[Gord Dibben]

Thanks Roger.

These old eyes did not pick up the difference in the piping.

Gord

 

[Excel Helps]

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.

 

[Excel Helps]

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)

 

[Excel Helps]

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

 

[Roger Govier]

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)

 

[Excel Helps]

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!