Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Summary Report

I have the data as shown in the snapshot below. It shows the input that I have and the output desired.

The names can be in any order. Sample File Attached.

How do I go about it?

Sid
Untitled.jpg
Sample-Workbook.xls
0
SiddharthRout
Asked:
SiddharthRout
1 Solution
 
jppintoCommented:
The only way that I see is building a macro for that Sid, with formulas it won't go...
0
 
kishoreb123Commented:
i feel a pivot table & then some concatenate functions will do ..
0
 
SiddharthRoutAuthor Commented:
@JP: Hmm I thought so too. Do you have a sample code ready?

kishoreb123: Umm, That will be messy. The file that I have has 9 million records :)

Sid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SiddharthRoutAuthor Commented:
One logic that I thought of following is [It's messier again :( ]

1) Looping through every cell and storing the value in an array by splitting it using SPACE as delimiter
2) Compare it with the next cell (The next cell also goes in an array using delim as SPACE). So basically comparing two arrays

Like I mentioned it is very messy. Any better solution?

Sid
0
 
jppintoCommented:
My suggestion:

1) Build a Pivot Table with the data to get the Names, Countries and Counts, like the attached image.
2) Build a macro to go through all of the lines on the Pivot Tables and build your output table

Hope you understand the logics.

jppinto
Capturar.JPG
0
 
SiddharthRoutAuthor Commented:
So basically it is the same thing what I suggested in my last post. Instead of looping through every cell in the first column of the Sheet, I now would be looping through the first column of Pivot and still use 2 arrays (as I mentioned above) to do the comparisons?

Sid
0
 
SiddharthRoutAuthor Commented:
Also like I mentioned above, the file has approx 9 millions rows so it is quite heavy. Wouldn't a Pivot make it even more heavier? My original plan was to delete the rows after creating the report...

Sid
0
 
jppintoCommented:
Let me check...I'm trying to get a solution done here. Wait a bit...
0
 
Saqib Husain, SyedEngineerCommented:
...the file has approx 9 millions rows...

And you are using excel for this?
0
 
SiddharthRoutAuthor Commented:
Majboori hai ssaqibh Bhai :(

I have to use Excel.

Sid
0
 
jppintoCommented:
I just can't make the VBA code to work! VBA is not my strongest point! :(

The ideia is, as I said, build the pivot table like I mentioned before. Then build the VBA code to go through each person's Name and build a string with each Country childitem of that PivotItem with also the Total number, separated by " | ", like you wanted, but I can't get it to work.

As VBA is your favorite part of Excel, can you try to build the code for that?

jppinto
0
 
SiddharthRoutAuthor Commented:
I already have started on that :)

Sid
0
 
sdwalkerCommented:
Sid,

I think the key is to use Dictionaries to keep track of where you are.  You'll need to add a reference to Microsoft Scripting Runtime.  Here is the spreadsheet that does what you want.  Of course, you'll need to find the beginning and ending rows of your data, but this will do what you need.  I have no idea how long it'll take with 9 million records.

Good luck,

sdwalker
sdw-Sample-Workbook.xls
0
 
SiddharthRoutAuthor Commented:
Ok part 1 achieved!!! :)

See if you or anyone else can improve on this code. If not then I will close this question...

Paste this in a module.

Option Explicit

Sub Sample()
    Dim SI As String
    
    SI = "Siddharth Rout | David Miller | Kumar Pradeep | Rout Siddharth | Kevin Jones | Miller David | Jones Kevin | Pradeep Kumar"
    
    '~~> Expected Output
    Debug.Print CheckNames(SI)
    'Siddharth Rout | David Miller | Kumar Pradeep | Kevin Jones
    
End Sub

Function CheckNames(strInput As String) As String
    Dim PipeA() As String, SpaceA() As String, SpaceB() As String
    Dim clearItem As Boolean, i As Long, j As Long, k As Long
    Dim strtemp As String
    
    PipeA = Split(strInput, "|")
    
    '~~> Remove Spaces
    For i = 0 To UBound(PipeA)
        PipeA(i) = Trim(PipeA(i))
    Next i
    
    '~~> Loop 1 through the array
    For i = 0 To UBound(PipeA)
        If Len(Trim(PipeA(i))) <> 0 Then
            '~~> Loop 2 through the array
            For j = 0 To UBound(PipeA)
                If i <> j Then
                    '~~> Check for Space and if found in both instances then possibility of a match
                    If InStr(1, PipeA(i), " ", vbTextCompare) > 0 And InStr(1, PipeA(j), " ", vbTextCompare) > 0 Then
                        SpaceA = Split(PipeA(i), " ")
                        SpaceB = Split(PipeA(j), " ")
                        '~~> If ubound is same then possibility of a match
                        If UBound(SpaceA) = UBound(SpaceB) Then
                            '~~> Check for each word in the other array
                            For k = 0 To UBound(SpaceA)
                                If k <> i Then
                                    If InStr(1, PipeA(j), SpaceA(k), vbTextCompare) Then
                                        clearItem = True
                                    Else
                                        '~~> Even if one word is different then NO MATCH
                                        clearItem = False
                                    End If
                                End If
                            Next k
                            '~~> If match found then clear the Array Item
                            If clearItem = True Then PipeA(j) = ""
                            
                            clearItem = False
                        End If
                    '~~> Since no Space do a direct Match
                    ElseIf UCase(PipeA(i)) = UCase(PipeA(j)) Then
                        '~~> If match found then clear the Array Item
                        PipeA(j) = ""
                    End If
                End If
            Next j
        End If
    Next i
    
    '~~> loop Through Array and Combine, omitting the blank cells
    For i = 0 To UBound(PipeA)
        If PipeA(i) <> "" Then strtemp = strtemp & " | " & PipeA(i)
    Next
    
    CheckNames = Mid(strtemp, 4)
End Function

Open in new window


Sid
0
 
SiddharthRoutAuthor Commented:
sdwalker: Sorry just saw your post.

Let me go through it.

Sid
0
 
SiddharthRoutAuthor Commented:
Fantastic sdwalker! :)

I will take it on from here :)

Sid
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now