Posts by Sissyfoo

Re: Generate a list of unique values

Hi,

Thanks for the replies. Unfortunately the advanced filter isn't really adequate for my needs. The list of unique values needs to be dynamic so that if the user updates list A then list B will update accordingly and spare the user the hassle of having to use entering it on both lists.

I have been playing about with arrays and a macro function which pulls out all the unique values in an array and puts it into a brand new array which can be displayed on the new worksheet. This *almost* solves my problem but has raised a few new ones (typically). I've attached a file which gives an example of the problems I'm having.

In worksheets 'June' and 'July' I have a list of animals (so to speak) and in the worksheet entitled 'Animals' I have compiled a list of the unique animals in Column E. This was done using the function UniqueItems (see code below).

Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
'   Accepts an array or range as input
'   If Count = True or is missing, the function returns the number
'   of unique elements
'   If Count = False, the function returns a variant array of unique
'   elements
    
    Dim Unique() As Variant ' array that holds the unique items
    Dim Element As Variant
    Dim i As Integer
    Dim FoundMatch As Boolean
        
'   If 2nd argument is missing, assign default value
    If IsMissing(Count) Then Count = True
        
'   Counter for number of unique elements
    NumUnique = 0
        
'   Loop thru the input array
    For Each Element In ArrayIn
        FoundMatch = False


'       Has item been added yet?
        For i = 1 To NumUnique
            If Element = Unique(i) Then
                FoundMatch = True
                GoTo AddItem '(Exit For-Next loop)
            End If
        Next i
       
AddItem:
'       If not in list, add the item to unique list
        If Not FoundMatch Then
            NumUnique = NumUnique + 1
            ReDim Preserve Unique(NumUnique)
            Unique(NumUnique) = Element
        End If
    
    Next Element
    
'   Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

The problems that have arisen are as follows:

I have used the UniqueItems() function to pull unique data from 'June' and place it in Column A in 'Animals' and done the same with 'July' in Column B. I then used UniqueItems() again on Columns A and B and put the final list of unique values in Column E. However, I understand that when using an array to transpose the data the ranges need to be of the same size (rows and columns). This creates a problem if my user only needs to add or remove an animal in either June or July because then the array in 'Animals' column E will be thrown completely out of whack and either be unable to pick up the new entry or return an entire column of errors.

I'm beginning to think I am barking up the wrong tree by trying to use arrays for this as it seems quite likely that the sizes of the initial arrays are going to vary from one month to the next and this is going to ruin the final array.

Does anyone have any other ideas which might help as this is really confusing the hell out of me. It's a shame because I really thought I had almost cracked it.

/edit Okay, I was going to attach the file I was talking about but for some reason it has bloated up to 7.30 mb. Grr.

//edit *sigh* Obviously having a blonde day. File attached.

Hello again,

I need to generate a new list of data taken from an existing list of data and then put this list in a new worksheet... but the trouble is that this existing list (call it 'list A') has many repeating values which I do not want in the new list (call this 'list B'). Normally I'd forgo the hassle of fiddling with macros for this but I need this to be a dynamic list so that the user can update list A and then list B will automatically correct itself without the user having to do it themselves.

For example:

List A

Monkey
Monkey
Turnip
Dinosaur
Dinosaur
Dinosaur
Wallaby
Pirate

List B

Monkey
Turnip
Dinosaur
Wallaby
Pirate

I'm sure that this is very easy to do but I just cannae think how to do it!

Any help or ideas would be greatly appreciated.

James

Re: Sum of cells which use a dark red font only

Hi,

Thanks for the speedy reply.

I found a chunk of code that you posted a while back (http://www.ozgrid.com/forum/showthread.php?t=27182) just minutes after I made my post and I was able to adapt it using the .Font expression and it gave me the perfect result.

lCol = rColor.Font.ColorIndex
     
    If SUM = True Then
        For Each rCell In rRange
            If rCell.Font.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell

Thanks again!

Hi,

I am trying to produce a spreadsheet that shows a column of figures for sales for each invidual person in a specific company. At the moment the spreadsheet sums the total sales amount for each company and lists it in dark red beneath the sales amount for each individual. What I need to do is produce a grand total at the very bottom of the list which produces the sum of all the entries which are written in the dark red font. I pretty confident that this can be done but I have no idea how to go about doing it.

I have attached an example of the type of thing I need to do to this post. The spreadsheet needs to be quite versatile because the people who will be using it will need to add/remove companies and sales figures as necessary and so I am trying to make it as easy as possible for them to add/remove stuff without fudging up the entire sheet and making all the calculations incorrect.

Grateful for any help you guys can give me.

James