move data to column with same value

[trenny]

I have data from one system I need to get into another system, and, of course, nothing matches. In Excel using vba, I would like to move data from one cell to another cell on the same row where the data moves to a column with that cell's value. I know this looks confusing, so...

One system exports values as follows:
<header row=""> teacher|grades taught
<row 1=""> Tom|6,7,8
<row 2=""> Ann|4,5,6,7,8
Using text to columns I have:
<row 1=""> Tom|6|7|8
<row 2=""> Ann|4|5|6|7|8

I need to have an import file like this (where the value is moved to the column with the matching header row value):
<header row=""> teacher|1|2|3|4|5|6|7|8|9|10|11|12
<row 1=""> Tom| | | | | |6|7|8| | | |
<row 2=""> Ann| | | |4|5|6|7|8| | | |

I tried to find the column reference based on header and then use the offset, but...
Thanks for any help.

</row></row></header></row></row></row></row></header>

 

[hiker95]

trenny,


Sample worksheets:

Excel Workbook
	A	B
1	teacher	grades taught
2	Tom	6,7,8
3	Ann	4,5,6,7,8
4
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	teacher	1	2	3	4	5	6	7	8	9	10	11	12
2
3
4
Results

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	teacher	1	2	3	4	5	6	7	8	9	10	11	12
2	Tom						6	7	8
3	Ann				4	5	6	7	8
4
Results

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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/13/2013
' http://www.mrexcel.com/forum/excel-questions/679069-move-data-column-same-value.html
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, nr As Long, fc As Long, s, i As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.Cells(1, 1).Resize(, 13).Value = [{"teacher","1","2","3","4","5","6","7","8","9","10","11","12"}]
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr Step 1
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  wR.Cells(nr, 1).Value = w1.Cells(r, 1).Value
  If InStr(w1.Cells(r, 2), ",") = 0 Then
    If w1.Cells(r, 2) <> "" Then
      fc = Cells(r, 2).Value + 1
      If fc > 0 Then
        wR.Cells(nr, fc).Value = w1.Cells(r, 2).Value
      End If
    End If
  Else
    s = Split(w1.Cells(r, 2), ",")
    For i = LBound(s) To UBound(s)
      fc = s(i) + 1
      If fc > 0 Then
        wR.Cells(nr, fc).Value = s(i)
      End If
    Next i
  End If
Next r
wR.Activate
Application.ScreenUpdating = True
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.

 

[trenny]

Hiker95,
Thanks and this worked great except when there is only one grade assigned to a teacher. Then I get the grade only in the first column.
I tried to figure this out myself, but can't!
From example below: Rows Kathleen and Jeffery are fine, but row Kathryn only has a 8 in the first column.

Kathleen	7,8,5,6
Kathryn	8
Jeffrey	2,3,4,5,6,7,8,1

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[hiker95]

trenny,

Nice catch.


Sample raw data:

Excel Workbook
	A	B
1	teacher	grades taught
2	Tom	6,7,8
3	Ann	4,5,6,7,8
4	Kathleen	7,8,5,6
5	Kathryn	8
6	Jeffrey	2,3,4,5,6,7,8,1
7
Sheet1

After the updated macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	teacher	1	2	3	4	5	6	7	8	9	10	11	12
2	Tom						6	7	8
3	Ann				4	5	6	7	8
4	Kathleen					5	6	7	8
5	Kathryn								8
6	Jeffrey	1	2	3	4	5	6	7	8
7
Results

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).

Option Explicit
Sub ReorgDataV2()
' hiker95, 01/18/2013
' http://www.mrexcel.com/forum/excel-questions/679069-move-data-column-same-value.html
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, nr As Long, fc As Long, s, i As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
With wR.Cells(1, 1).Resize(, 13)
  .Value = [{"teacher","1","2","3","4","5","6","7","8","9","10","11","12"}]
  .Font.Bold = True
  .HorizontalAlignment = xlCenter
End With
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr Step 1
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  wR.Cells(nr, 1).Value = w1.Cells(r, 1).Value
  If InStr(w1.Cells(r, 2), ",") = 0 Then
    If w1.Cells(r, 2) <> "" Then
      fc = w1.Cells(r, 2).Value + 1
      If fc > 0 Then
        wR.Cells(nr, fc).Value = w1.Cells(r, 2).Value
      End If
    End If
  Else
    s = Split(w1.Cells(r, 2), ",")
    For i = LBound(s) To UBound(s)
      fc = s(i) + 1
      If fc > 0 Then
        wR.Cells(nr, fc).Value = s(i)
      End If
    Next i
  End If
Next r
wR.Cells.EntireColumn.AutoFit
wR.Activate
Application.ScreenUpdating = True
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 ReorgDataV2 macro.

 

[trenny]

Works great, thanks.
It was a worksheet reference, correct? I learn more everytime I get help!

 

[hiker95]

trenny,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

It was a worksheet reference, correct?

Yes, but there never was a single entry in your raw data to check against.

I learn more everytime I get help!

Training / Books / Sites as of 12/30/2012

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
MrExcel's Products: Books, CDs, Podcasts

There are over 1800 Excel videos/tutorials here:
excelisfun -- 1900 Excel How To Videos - YouTube

Getting Started with VBA.
DataPig Technologies

If you are serious about learning VBA try
Macros Made Easy for Microsoft Excel

Excel Tutorials and Tips - VBA - macros - training
Excel Tutorial | Excel Tips | Excel Articles

Excel 2007 VBA materials to learn here:
VBA for Excel 2007 tutorial-VBA programming?

Here's a good primer on the scope of variables.
Understanding Scope

See David McRitchie's site if you just started with VBA
Getting Started with Macros and User Defined Functions

What is a Visual Basic Module?
What Is A VBA Module?

Here is a good introductory tutorial using a VBA Class:
Classes In VBA

Ron de Bruin's intro to macros:
Where do I paste the code that I want to use in my workbook

Ron's Excel Tips
Ron's Excel Tips

Anthony's Excel VBA Page - Excel Application - Excel Consultant - Excel Consulting (see 3 tutorials in Excel VBA Basic Tutorial Series)

http://www.jlathamsite.com/Teach/VBA...troduction.pdf (95 page "book")

BET: Microsoft Excel Visual Basic

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
Creating An XLA Add In

How do I create a PERSONAL.XLS(B) or Add-in
How do I create a PERSONAL.XLS(B) or Add-in

Creating custom functions
Creating custom functions - Excel - Office.com

Writing Your First VBA Function in Excel
Writing Your First VBA Function in Excel

VBA for Excel (Macros)
Excel Macros (VBA) Tutorial

Excel Macros Tutorial
Excel Macros (VBA) Tutorial

Excel Macros & Programming
Learning about EXCEL macros

VBA Lesson 11: VBA Code General Tips and General Vocabulary
VBA for Excel macros language

Excel VBA -- Adding Code to a Workbook
Excel VBA -- Adding Code to Excel Workbook

Beyond Excel's recorder

Helpful as a simple concise reference for the basics, and, using the macro recorder is endlessly powerful.
MS Excel: Cells

Learn to debug:
Debugging VBA

How To: Assign a Macro to a Button or Shape
How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts

User Form Creation
Create an Excel UserForm

When To Use a UserForm & What to Use a UserForm For
When to use Userform & What To Use Them For. Excel Training VBA 2 lesson 2

Excel Tutorials / Video Tutorials - Functions
Excel VLookup Function Examples

INDEX MATCH - Excel Index Function and Excel Match Function
Excel Index Function and Match Function

Excel Data Validation
Excel Data Validation Tips and Quirks
Excel Data Validation - Add New Items

Your Quick Reference to Microsoft Excel Solutions
XL-CENTRAL.COM : For your Microsoft Excel Solutions

New! Excel Recorded Webinars
DataPig Technologies

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
Programming In The VBA Editor

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…

by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Professional Excel Development

by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
Excel 2002 VBA: Programmers Reference

"Professional Excel Development" by Rob Bovey, Dennis Wallentin, Stephen Bullen, & John Green

DonkeyOte: My Recommended Reading, Volatility
Volatile Excel Functions -Decision Models

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
Excel: Introduction to Array Formulas - Xtreme Visual Basic Talk
Array in Excel VBA

Shortcut Keys in Excel 2000 through 2007

Pivot Intro
Using Pivot Tables and Pivot Charts in Microsoft Excel
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
Excel Pivot Table -- Dynamic Data Source
Overview of PivotTable and PivotChart reports - Excel - Office.com

Email from XL - VBA
Example Code for sending mail from Excel

Outlook VBA
Writing VBA code for Microsoft Outlook

Function Dictionary
http://www.xlfdic.com/

Function Translations
Excel 2007 function name translations - Dictionary Chart Front Page

Dynamic Named Ranges
Excel Names -- Excel Named Ranges

How to create Excel Dashboards
http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
Excel Dashboard Templates
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Free Microsoft Excel Dashboard Widgets to Download
AJP Excel Information - Gauge

Excel Dashboard / Scorecard Ebook
Excel Dashboards and Scorecards Ebook | How to Create Dashboards in Excel

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Templates
CPearson.com Topic Index
Excel Template - Golf Scores
Free Microsoft Excel Template Links & Search Engine

Microsoft Excel Cascading Listboxes Tutorial
Microsoft Excel Cascading Listboxes Tutorial - YouTube

Date & Time stamping:
McGimpsey & Associates : Excel : Time and date stamps

Get Formula / Formats thru custom functions:
Show FORMULA or FORMAT of another cell

A nice informative MS article "Improving Performance in Excel 2007"
Improving Performance in Excel 2007

Progress Meters
AJP Excel Information - Progress meters
Website Disabled

And, as your skills increase, try answering posts on sites like:
MrExcel.com | Excel Resources | Excel Seminars | Excel Products
Excel Help Forum
Excel Templates | Excel Add-ins and Excel Help with formulas and VBA Macros
VBA Express Portal
Excel, Access, PowerPoint and Word VBA Macro Automation Help

If you are willing to spend money for the training, then something here should work for you...
Amazon.com: excel tutorial dvd
Amazon.com: excel tutorial dvd