Learn how to a build a cloud-first strategyRegister Now

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

# 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
1 Solution

Commented:
The only way that I see is building a macro for that Sid, with formulas it won't go...
0

Commented:
i feel a pivot table & then some concatenate functions will do ..
0

Author 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

Author 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

Commented:
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

Author 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

Author 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

Commented:
Let me check...I'm trying to get a solution done here. Wait a bit...
0

EngineerCommented:
...the file has approx 9 millions rows...

And you are using excel for this?
0

Author Commented:
Majboori hai ssaqibh Bhai :(

I have to use Excel.

Sid
0

Commented:
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

Author Commented:
I already have started on that :)

Sid
0

Commented:
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

Author 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
``````

Sid
0

Author Commented:
sdwalker: Sorry just saw your post.

Let me go through it.

Sid
0

Author Commented:
Fantastic sdwalker! :)

I will take it on from here :)

Sid
0

## Featured Post

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