=INDEX =Headache...

[ourfear]

Hi Peeps,

Im working on what i thought would have been an easy spreadsheet.

Basically Im looking up data from a table, then im using the index function to sort the data.

This all works fine.e.g: =INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=$E$1,ROW($A$1:$B$7)),ROW(1:2)),3)

Which looks up the text in E1 and then lists the activities that have been associated with it on a table.

What im then trying to do is to simply amalgamate these references into one cell so:

=E1&" , "&E2

The problem being that E1 = Activity 1 and E2 = #Num! as no data has been filled in on the relavent table yet. Now the =E1&" , "&E2 wont work as it also just shows #Num!

I finally fogured out that if i place a second coloumn next to my data collector that reads:

=IF(ISERROR(E1),"Error1",E1)

Then i can still combine the cells but with ERROR1 written in the cell rather then #Num!.

Now im trying to write a macro or a function that will remove the Error1 refference from the text string in my merged cell.

Complicated...yes..make sense...hope so!!

Just in case here is a link to my work sheet:

http://www.mediafire.com/?fhjtmaau45x

Many Thanks.

Russ

 

[Andrew Poulsom]

Try in E2:

=IF((COUNTIF($A$1:$A$7,E$1)+COUNTIF($B$1:$B$7,E$1))>(ROW(A1)-ROW(A$1)),INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=E$1,ROW($A$1:$B$7)),ROW(1:2)),3),"")

 

[ourfear]

Wey hey!!! Many Thanks Andrew!! That just saved me an awful lot of time!!
My next issue is that my Import page still shows:
<table x:str="" style="border-collapse: collapse; width: 559px; height: 54px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 176pt;" width="235"><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 176pt;" x:str="Activity 1 - One , Activity 2 - Two , , , , , , , , , , , , , , , , , , , , , , , , , , , " width="235" height="20">Activity 1 - One , Activity 2 Two , , , , , , , , , , , , , , , , , , , , , , , , , , , </td> </tr></table>
and the values need to be Comma Separated as it's going to be imported into a third party modelling program.

But I think it's not possible to be honest and your help on this issue has been immense!! : )

 

[Andrew Poulsom]

Maybe you could use this custom function:

Function ConcatNoBlanks(Rng As Range) As String
    Dim Cell As Range
    ConcatNoBlanks = ""
    For Each Cell In Rng.Cells
        If Len(Cell.Value) > 0 Then
            ConcatNoBlanks = ConcatNoBlanks & Cell.Value & ","
        End If
    Next Cell
    If Len(ConcatNoBlanks) > 0 Then
        ConcatNoBlanks = Left(ConcatNoBlanks, Len(ConcatNoBlanks) - 1)
    End If
End Function

 

[ourfear]

How would i Use that?

Might sound daft but i know loads about Excel except the full VB side?! : )

 

[Andrew Poulsom]

Press Alt+F11 to go to the Visual Basic Editor. Click your workbook in the Project window and choose Insert|Module from the menu. Paste my code into the window on the right. Close the VBE by clicking the X top right. In your worksheet you can use the function like this:

=ConcatNoBlanks(A1:C1)

 

[ourfear]

Slight modification to =ConcatNoBlanks(Code!F2:Code!F3:Code!F4:Code!F5) and it works absolutely 100% perfectly!! Plus our MooD software can now import it and model it happily!!!!

My man you are most definitely an MVP in this forum!!! Your help has been greatly appreciated.

: )

 

[Andrew Poulsom]

Shouldn't it be?

=ConcatNoBlanks(Code!F2:F5)

 

[ourfear]

Yup, that what i was doing!! : ) I posted it then though...BUHH!! lol