Interview Question: Excel dataset scenario

[01's]

Hi,

I have an Excel database with around 1000 rows of data. Data are arranged as below with around 300 names and with multiple skills for each Name.

Name skill
Shrinivas Excel
Shrinivas VBA
Veer C++
Veer UID
Veer Analytics

I need output like below using excel function and VBA both separately. Thanks for your help!

Name skill1 skill2 skill3
Shrinivas Excel VBA
Veer C++ UID Analytics

 

[hiker95]

01's,

With your raw data in in Columns A, and, B, the results will be written beginning in cell D1.

Try the following macro.

Sub ReroganizeData()
' hiker95, 08/24/2017, ME1020294
' Thank you MickG
Dim Dic As Object, Ray As Variant, k As Variant, G As Variant
Dim Rw As Long, Ac As Long, c As Long, a As Long, oMax As Long
Ray = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For Rw = 1 To UBound(Ray, 1)
  For Ac = 2 To UBound(Ray, 2)
    If Not Dic.Exists(Ray(Rw, 1)) Then
      Set Dic(Ray(Rw, 1)) = CreateObject("Scripting.Dictionary")
    End If
    Dic(Ray(Rw, 1))(Ray(Rw, Ac)) = Empty
  Next Ac
Next Rw
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1) * UBound(Ray, 2))
For Each k In Dic.Keys
  c = c + 1: a = 1
  nray(c, 1) = k
  For Each G In Dic(k)
    a = a + 1
    nray(c, a) = G
    oMax = Application.Max(oMax, a)
  Next G
Next k
Range("D1").Value = Range("A1").Value
With Range("D2").Resize(c, oMax)
  .Value = nray
  Columns(4).Resize(, oMax).AutoFit
End With
With Range("E1").Resize(, oMax - 1)
  .Formula = "=""skill"" & Column() - 4"
  .Value = .Value
End With
Columns.AutoFit
End Sub

 

[01's]

Works like a charm!!!
Thanks Hiker95
The interviewer had asked me whether this is possible through Excel formulae and which one. Any idea on that?

01's,

With your raw data in in Columns A, and, B, the results will be written beginning in cell D1.

Try the following macro.

Sub ReroganizeData()
' hiker95, 08/24/2017, ME1020294
' Thank you MickG
Dim Dic As Object, Ray As Variant, k As Variant, G As Variant
Dim Rw As Long, Ac As Long, c As Long, a As Long, oMax As Long
Ray = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For Rw = 1 To UBound(Ray, 1)
  For Ac = 2 To UBound(Ray, 2)
    If Not Dic.Exists(Ray(Rw, 1)) Then
      Set Dic(Ray(Rw, 1)) = CreateObject("Scripting.Dictionary")
    End If
    Dic(Ray(Rw, 1))(Ray(Rw, Ac)) = Empty
  Next Ac
Next Rw
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1) * UBound(Ray, 2))
For Each k In Dic.Keys
  c = c + 1: a = 1
  nray(c, 1) = k
  For Each G In Dic(k)
    a = a + 1
    nray(c, a) = G
    oMax = Application.Max(oMax, a)
  Next G
Next k
Range("D1").Value = Range("A1").Value
With Range("D2").Resize(c, oMax)
  .Value = nray
  Columns(4).Resize(, oMax).AutoFit
End With
With Range("E1").Resize(, oMax - 1)
  .Formula = "=""skill"" & Column() - 4"
  .Value = .Value
End With
Columns.AutoFit
End Sub

 

[DHayes]

Morning,
Yes there is a formula that you can use.
You need to make the names Unique.
see example below.
[TABLE="class: grid, width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]skill[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]Skill 1[/TD]
[TD="width: 64, align: right"]Skill 2[/TD]
[TD="width: 64, align: right"]Skill 3[/TD]
[/TR]
[TR]
[TD]Shrinivas[/TD]
[TD]Excel[/TD]
[TD][/TD]
[TD]Shrinivas[/TD]
[TD]Excel[/TD]
[TD]VBA[/TD]
[TD="align: center"]No Skill[/TD]
[/TR]
[TR]
[TD]Shrinivas[/TD]
[TD]VBA[/TD]
[TD][/TD]
[TD]Veer[/TD]
[TD]C++[/TD]
[TD]UID[/TD]
[TD]Analytics[/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]C++[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]UID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]Analytics[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula below.
Use SHFT+CTL+ENTER not just enter

{=IFERROR(INDEX($B$2:$B$6,SMALL(IF($D2=$A$2:$A$6,ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1,""),COLUMN(A1))),"No Skill")}

 

[hiker95]

Works like a charm!!!
Thanks Hiker95

01's,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[01's]

Sure, Thanks.

 

[01's]

Wow Great!!! Thanks DHayes. Another magic formula. Loved it.

Morning,
Yes there is a formula that you can use.
You need to make the names Unique.
see example below.
[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]skill[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]Skill 1[/TD]
[TD="width: 64, align: right"]Skill 2[/TD]
[TD="width: 64, align: right"]Skill 3[/TD]
[/TR]
[TR]
[TD]Shrinivas[/TD]
[TD]Excel[/TD]
[TD][/TD]
[TD]Shrinivas[/TD]
[TD]Excel[/TD]
[TD]VBA[/TD]
[TD="align: center"]No Skill[/TD]
[/TR]
[TR]
[TD]Shrinivas[/TD]
[TD]VBA[/TD]
[TD][/TD]
[TD]Veer[/TD]
[TD]C++[/TD]
[TD]UID[/TD]
[TD]Analytics[/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]C++[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]UID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veer[/TD]
[TD]Analytics[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula below.
Use SHFT+CTL+ENTER not just enter

{=IFERROR(INDEX($B$2:$B$6,SMALL(IF($D2=$A$2:$A$6,ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1,""),COLUMN(A1))),"No Skill")}

 

[DHayes]

Thanks for your feedback 01's. Pleasure to help.