PDA

View Full Version : Need help to create an Excel Function



zerokreap
05-31-2011, 01:09 PM
It has been years since I have done any programming, so I apologize in advance for my ignorance.

I have an Excel function that I would like to write, but do not know VBA commands or syntax at all.

I need a function that will scan a set of selected sells in one sheet, and produce output in another. In this column of cells I would have courses listed. For example:

EN102
EN400
SP101
EN102
POLI330
SP101

The function would go through this list, and produce a "de-duped" list of sorts (courses listed more than once in the original, would be listed only once in the output with a "-" and the number of times it appeared next to it. For example, the output for the list above would look like this:

EN102 - 2
EN400
SP101 - 2
POLI330

I assume this would use a loop that would compare the text string in the first cell to all other cells, then the second cell, and so on.

Any ideas on this?

Thanks in advance!

Bob Phillips
05-31-2011, 01:32 PM
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim numDups As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow '

numDups = Application.CountIf(.Columns(1), .Cells(i, "A").Value2)
If numDups > 1 Then

.Cells(i, "A").Value2 = .Cells(i, "A").Value2 & " - " & numDups
End If
Next i

For i = Lastrow To 2 Step -1

If Application.Evaluate("SUMPRODUCT(--(LEFT(A1:A" & i - 1 & ",LEN(A" & i & "))=A" & i & "))") > 0 Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

zerokreap
05-31-2011, 01:43 PM
Wow, that was quick! So, would this code function like a macro, where you select your range, then run it? Is there a way to use it like a function, so that it carries out its function automatically, as with the SUM function, for example?

zerokreap
05-31-2011, 02:05 PM
My spreadsheet setup is like this:
8 instructors have a workbook with 12 sheets in it (one for each month). They enter data into their own sheets, and a 9th workbook is automatically updated in real-time via formula mapping. I know how to handle numbers, but I need a function that will go through everyone's course data they have entered and return a de-duped list in that 9th spreadsheet, which is set up as a summary report of sorts. Just like the Excel Sum function can return sum data from several places to a central point, I want to return text data to a central point so that the head of the department can see what courses are being taught. Is this even possible?