Data from columns - counting multiple occurrence of values in cells & displaying the columns of occurence

[ashariff]

Hi All

I have just joined this forum as i have been a following many posts for my queries, have found your expertise and advice very useful for many issues.

I have been scratching my head on the following data spreadsheet 'Master Recall' for more than a week without any positive outcome. (unfortunately I am not able to upload the spreadsheet for some reason)

The idea is that there can be any value of numbers in any number columns in sheet 1 (inc any number of rows)

Each column is labeled to indicate the topic of the respective column

There are definitely multiple occurrences of numbers across any number of columns as can be seen

I have the following objective to achieve from the data in sheet 1

1) Displaying a merged list of numbers in a single column (in ‘sheet 2’ 1st column) from all values in ‘sheet 1’ - i.e. removing all duplicates

2) The 2nd column in ‘sheet 2’ to calculate & display the frequency of occurrence of each number from ‘sheet 1’ i.e. adjoining with numbers of merged list (1) above

3) It is quite possible that a number will appear in many columns in ‘sheet 1’ - hence the 3rd column from ‘sheet 2’ to list the column topic (from first column cell) where the number occurrences have been found. e.g. if 17069 appears in column 1 & column 5 in ‘sheet 1’ then 3rd column in 'sheet 2' to display AF 7 and 4th column to display BP 4

I will really appreciate advice on how I can achieve this…..

 

[Legacy 234512]

At least attach a screenshot ( erase your data ).

 

[Legacy 234512]

Try this and let me know if this what you meant

Option Explicit


Sub calc()
    Dim i As Long
    Dim lastRow As Long
    Dim arr() As Variant
    lastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    ReDim Preserve arr(lastRow - 1)
    For i = 2 To lastRow
        arr(i - 2) = Sheets("sheet1").Cells(i, 1)
    Next i
    Call RemoveDuplicate(arr)
    For i = 2 To UBound(arr) + 2
        Sheets("sheet2").Cells(i, 1) = arr(i - 2)
    Next i
    lastRow = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        Sheets("Sheet2").Cells(i, 2) = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Sheets("Sheet1").Cells(i, 1))
    Next i
End Sub


Public Sub RemoveDuplicate(ByRef StringArray() As Variant)
    Dim LowBound As Long, UpBound As Long
    Dim TempArray() As Variant, Cur As Long
    Dim A As Long, B As Long


    If (Not StringArray) = True Then Exit Sub
    LowBound = LBound(StringArray)
    UpBound = UBound(StringArray)
    ReDim TempArray(LowBound To UpBound)
    Cur = LowBound
    TempArray(Cur) = StringArray(LowBound)
    For A = LowBound + 1 To UpBound
        For B = LowBound To Cur
            If LenB(TempArray(B)) = LenB(StringArray(A)) Then
                If InStrB(1, StringArray(A), TempArray(B), vbBinaryCompare) = 1 Then Exit For
            End If
        Next B
        If B > Cur Then Cur = B: TempArray(Cur) = StringArray(A)
    Next A
    ReDim Preserve TempArray(LowBound To Cur)
    StringArray = TempArray
End Sub

Sheet1 Column A

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]header(SHEET1)
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[ashariff]

Hi Mehowski,

My sincere apologies for not being able to reply earlier

I have now managed to upload the spreadsheet to my dropbox, pls find the link below

https://www.dropbox.com/sh/8a2vlwi4b4dy2f3/Y8oUzkYx2a?m

Thank you so much for your code, it is working for the first column of data, perhaps my spreadsheet will provide you with more information on my ideal outcome

The idea is that there can be as many columns in 'sheet 1' and each column has a column topic in the first cell.

The number of rows in each column can differ with the values

Based on the information from 'sheet 1'

In 'Sheet 2'

Column A : A merged list of all number values from 'Sheet 1' is created
Column B: Frequesncy of occurrence of each of the numbers in column A
Column C Onwards: Based on the occurrence, the individual column topics from 'Sheet 1' where the number value from Column A appears i.e. If 17069 appears in 1st and 5th columns in 'Sheet 1' - then in Column C the text is AF4 and in Column D the value is BP 5.

Ofcourse come numbers will appear for more than 10 times etc. in each case the column topic added in the same way above...

I hope i am making sense.....

Sorry for delayed reply....