list creation assistance Vlookup? if statement? VBA code?

[Javi]

Hi All,</SPAN>

I am looking for some advice or direction on how to assemble multiple list from a (2) column of data set. Here is what I have as a data set in column “A” & "B".
</SPAN>
FYI..I did come up with a sort/filter copy and paste solution code. However, I think it could be very error-prone.

I just cannot come up with any type of if statement and/or the Vlookup that would work

Any suggestions or help would be appreciated.
</SPAN>
Data set: Column A - classroom numbers --- Column B student names example:
</SPAN>
A1-101 B1-Jim</SPAN>
A2-101 B2-Cindy</SPAN>
A3-102 B3-Tim</SPAN>
A4-102 B4-Dave</SPAN>
A5-102 B5-Mary</SPAN>
A6-102 B6-Bob</SPAN>
A7-103 B7-Chris</SPAN>
A8-103 B8-Sue</SPAN>
A9-104 B9-Jan</SPAN>

This is what my destination looks like D1:AM50- will always have the value “classroom” and D2:AM50 will always represent the individual “room numbers”.</SPAN>

Column D</SPAN>
D1-Classroom</SPAN>
D2-101</SPAN>
D3-Jim</SPAN>
D4-Cindy</SPAN>

Column E</SPAN>
E1-Classroom</SPAN>
E2-102</SPAN>
E3-Tim</SPAN>
E4-Dave</SPAN>
E5-Mary</SPAN>
E6-Bob</SPAN>

Column F</SPAN>
F1-Classroom</SPAN>
F2-103</SPAN>
F7-Chris</SPAN>
F8-Sue</SPAN>
F9-Jan</SPAN>


Column H</SPAN>
H1-Classroom</SPAN>
H2-104</SPAN>
H9-Jan</SPAN>


Thanks again!!

 

[Javi]

The answer/solution turned out to be a formula. Remember if you happen to use this formula after you paste/enter it into the formula bar you must press "control" & " shift" & " enter" simultaneously so it will insert the brackets around the whole formula that are needed.


Basically this formula will return multiple values based on a single lookup. ie: look up a value in a list and return multiple corresponding values.



=IF(ISERROR(INDEX($A$2:$B$1000,SMALL(IF($A$1:$A$1000=F$1,ROW($A$1:$A$1000)),ROW(1:1)),2)),"",INDEX($A$1:$B$1000,SMALL(IF($A$1:$A$1000=F$1,ROW($A$1:$A$1000)),ROW(1:1)),2))

 

[hiker95]

Javi,

The below macro will adjust for a varying number of rows.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	101	Jim
2	101	Cindy
3	102	Tim
4	102	Dave
5	102	Mary
6	102	Bob
7	103	Chris
8	103	Sue
9	104	Jan
10
Sheet1

After the macro using the Scripting.Dictionary - Thank you MickG:

Excel 2007
	A	B	C	D	E	F	G	H
1	101	Jim		Classroom	Classroom	Classroom	Classroom
2	101	Cindy		101	102	103	104
3	102	Tim		Jim	Tim	Chris	Jan
4	102	Dave		Cindy	Dave	Sue
5	102	Mary			Mary
6	102	Bob			Bob
7	103	Chris
8	103	Sue
9	104	Jan
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 12/06/2013
' http://www.mrexcel.com/forum/excel-questions/743073-list-creation-assistance-vlookup-if-statement-visual-basic-applications-code.html
' the original code my MickG has been modified
' Thank you: MickG , 2 / 2 / 2011, MG02Feb42
Dim rng As Range, dn As Range
Dim q As Variant
Dim omax As Integer
Dim n As Long, lc As Long
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To rng.Count, 1 To Columns.Count)
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each dn In rng
    If Not .Exists(dn.Value) Then
      n = n + 1
      .Add dn.Value, Array(n, 2)
      Ray(n, 1) = dn.Value: Ray(n, 2) = dn.Offset(, 1)
    Else
      q = .Item(dn.Value)
      q(1) = q(1) + 1
      Ray(q(0), q(1)) = dn.Offset(, 1)
      .Item(dn.Value) = q
      omax = Application.Max(q(1), omax)
    End If
  Next
  Range("D2").Resize(omax, .Count) = Application.Transpose(Ray)
  lc = Cells(2, Columns.Count).End(xlToLeft).Column
  Range(Cells(1, 4), Cells(1, lc)) = "Classroom"
  Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[Javi]

Thanks Hiker95.... Works great very clean.... Mucher better... however I do have one issue it fails with over 300 lines. at "Range("D2").Resize(omax, .Count) = Application.Transpose(Ray)
"

unfortunately I do not see or understand where the code has any quantity limits may be you can help me.

 

[hiker95]

Javi,

The code did work correctly for the data you described.

In order to continue I will have to see your actual raw data.


For the amount of rows you have mentioned:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Javi]

Thanks for your reply.

I have apr 30 Branches/Classrooms in column "A" and over 400 names in Column "B"

https://app.box.com/s/cbxtwhl6gmyxyali9nkt

 

[hiker95]

Javi,

Thanks for the workbook.

Would you like the Branch numbers in range D2:AA2 sorted numerically ascending left to right, like this?

And, would you like the names in the above columns sorted alphabetically ascending?

Excel 2007
	D	E	F	G
1	Branch	Branch	Branch	Branch
2	6020	6030	6040	6050
Listbyloc

 

[Javi]

yes that would be preferred .. Would this also work if the branch numbers happened to contain letters? in other words instead of having a branch number maybe we would have a person's/branch name there (not a numeric value)?

 

[hiker95]

Javi,

Would this also work if the branch numbers happened to contain letters?

If they do contain numbers and letters, then I would like to see another workbook.

And, would you like the names in the above columns sorted alphabetically ascending?

 

[Javi]

no they do not contain both at this time I was just thinking of future use for this code.