Transpose - repeat until change

[jmh2008]

I have an excel spreadsheet with member names and a history of all they share purchases. The row Labels are:

Last name | First Name | Purchase 1 | Purchase 2 | Purchase 3| Purchase Date 1 | Purchase date 2 | Purchase date 3

Purchases equal the Amount, which corresponds to the Date, ie

Purchase 1 = Purchase Date 1
Purchase 2 - Purchase Date 2,
etc

I want to have the following so I can import to Access: (Not all members purchase the same amount)

Last name | First name | Purchase Dates | Amount
Joe Brown may 1, 2011 10
Joe Brown Oct 2,, 2010 10
Joe Brown Feb 11, 2007 10
Mary Smith July 1, 2000 10
Frank Jones Nov 1, 2000 10
Frank Jones March 4, 1999 10

Thanks!!

 

[hiker95]

jmh2008,

We can not tell by your posted data where titles/data begin (rows and columns).


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[jmh2008]

http://www.box.net/shared/qqr18c1fdb

I wish I could figure out the add on for image c. What do you do, create an image and then copy to something like piscas?

 

[jmh2008]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Number	Last Name	First Name	Purchase 1	Purchase 2	Purchase 3	Purchase 4	Purchase 5	Purchase 1 Date	Purchase 2 Date	Purchase 3 Date	Purchase 4 Date	Purchase 5 Date
2	1	Doe	John	10	10	10			30/09/1977	30/09/1978	30/09/1979
3	2	Smith	Mary	10	10	10	10	10	30/09/1977	30/09/1978	30/09/1979	29/09/1980	29/09/1981
4	3	Ball	Game	10	10	10	10	10	30/09/1977	30/09/1978	30/09/1979	29/09/1980	29/09/1981
5	4	Johnston	Frank	10	10	10			30/09/1977	30/09/1978	30/09/1979
6	5	Curry	Jane	10					30/09/1977
7
8
9
10	Number	Last Name	First name	Amount	Date
11	1	Doe	John	10	30/09/1977
12	1	Doe	John	10	30/09/1978
13	1	Doe	John	10	30/09/1979
14	2	Smith	Mary	10	30/09/1977
15	2	Smith	Mary	10	30/09/1978
16	2	Smith	Mary	10	30/09/1979
17	2	Smith	Mary	10	29/09/1980
18	2	Smith	Mary	10	29/09/1981
19	3	Ball	Game	10	30/09/1977
20	3	Ball	Game	10	30/09/1978
21	3	Ball	Game	10	30/09/1979
22	3	Ball	Game	10	29/09/1980
23	3	Ball	Game	10	29/09/1981
24	3	Ball	Game	10	29/09/1982
25	3	Ball	Game	10	29/09/1983
26	4	Johnston	Frank	10	30/09/1977
27	4	Johnston	Frank	10	30/09/1978
28	4	Johnston	Frank	10	30/09/1979
29	5	Curry	Jane	10	30/09/1977
Sheet1

Excel 2007

 

[hiker95]

jmh2008,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Number	Last Name	First Name	Purchase 1	Purchase 2	Purchase 3	Purchase 4	Purchase 5	Purchase 1 Date	Purchase 2 Date	Purchase 3 Date	Purchase 4 Date	Purchase 5 Date
2	1	Doe	John	10	10	10			30/09/1977	30/09/1978	30/09/1979
3	2	Smith	Mary	10	10	10	10	10	30/09/1977	30/09/1978	30/09/1979	29/09/1980	29/09/1981
4	3	Ball	Game	10	10	10	10	10	30/09/1977	30/09/1978	30/09/1979	29/09/1980	29/09/1981
5	4	Johnston	Frank	10	10	10			30/09/1977	30/09/1978	30/09/1979
6	5	Curry	Jane	10					30/09/1977
7
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E
1	Number	Last Name	First Name	Amount	Date
2	1	Doe	John	10	30/09/1977
3	1	Doe	John	10	30/09/1978
4	1	Doe	John	10	30/09/1979
5	2	Smith	Mary	10	30/09/1977
6	2	Smith	Mary	10	30/09/1978
7	2	Smith	Mary	10	30/09/1979
8	2	Smith	Mary	10	29/09/1980
9	2	Smith	Mary	10	29/09/1981
10	3	Ball	Game	10	30/09/1977
11	3	Ball	Game	10	30/09/1978
12	3	Ball	Game	10	30/09/1979
13	3	Ball	Game	10	29/09/1980
14	3	Ball	Game	10	29/09/1981
15	4	Johnston	Frank	10	30/09/1977
16	4	Johnston	Frank	10	30/09/1978
17	4	Johnston	Frank	10	30/09/1979
18	5	Curry	Jane	10	30/09/1977
19
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, 05/02/2011
' http://www.mrexcel.com/forum/showthread.php?t=547206
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, LC As Long, a As Long, aa As Long
Dim FG As Long, NG As Long, o As Long, NR 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.UsedRange.Clear
wR.Range("A1:E1") = [{"Number","Last Name","First Name","Amount","Date"}]
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
FG = 4
NG = 4 + ((LC - 3) / 2)
o = (LC - 3) / 2
For a = 2 To LR Step 1
  For aa = FG To NG - 1 Step 1
    If w1.Cells(a, aa) <> "" Then
      NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wR.Cells(NR, 1).Resize(, 3).Value = w1.Range("A" & a).Resize(, 3).Value
      wR.Cells(NR, 4).Value = w1.Cells(a, aa).Value
      wR.Cells(NR, 5).Value = w1.Cells(a, aa).Offset(, o).Value
    End If
  Next aa
Next a
wR.Range("E2:E" & NR).NumberFormat = "dd/mm/yyyy"
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[hiker95]

jmh2008,

The above macro should also work for a varying number of Purchase # and Purchase # Date columns, as long as both sets of columns contain the same number of columns.

 

[Peter_SSs]

jmh2008,

The above macro should also work for a varying number of Purchase # and Purchase # Date columns, as long as both sets of columns contain the same number of columns.

I have also assumed this to be the case, that the data starts in cell A1 per the sample and that the original data sheet is the active sheet when the code is run.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> fc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.Copy After:=ActiveSheet<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        fc = .Cells(1, .Columns.Count).End(xlToLeft).Column + 2<br>        cols = (fc - 5) / 2<br>        .Cells(1, fc).Resize(, 5).Value = _<br>            Array("Number", "Last Name", "First name", "Amount", "Date")<br>        LR = .Cells(.Rows.Count, 1).End(xlUp).Row<br>        fr = 2<br>        <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>            .Cells(fr, fc + 3).Resize(cols).Value = _<br>                Application.Transpose(.Cells(r, 4).Resize(, cols).Value)<br>            .Cells(fr, fc + 4).Resize(cols).Value = _<br>                Application.Transpose(.Cells(r, 4 + cols).Resize(, cols).Value)<br>            rws = .Cells(.Rows.Count, fc + 3).End(xlUp).Row - fr + 1<br>            .Cells(fr, fc).Resize(rws, 3).Value = .Cells(r, 1).Resize(, 3).Value<br>            fr = fr + rws<br>        <SPAN style="color:#00007F">Next</SPAN> r<br>        .Columns("A").Resize(, fc - 1).Delete<br>        .Columns("A:E").AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jmh2008]

That worked great. OK, so this was my sample database, my actual spreadsheet has many row headings, but I just needed this completed for that one section. Is there a place in your VBA code where I can specify the actually row heading labels to do this in the actual spreadsheet?

Thanks, I so wish I could work VBA!!! Still working on getting my head around Index, match and choose,,,,,,

Here are my row labels:


FirstName
Lastname
StreetAddress
City
State
Zip
MainPhone
MainEmail
Share Notes
Notes
MembershipDate
MembershipStatus
Join Notes
ExpirationDate
Share In Full
Dues
Category
League
Day
Men's
Ladies
Mixed
Open
Social
Juniors
Volunteer
Past Member
Title
Share1
Share2
Share3
Share4
Share5
Share6
Share7
Share8
Share9
Share10
Date1
Date2
Date3
Date4
Date5
Date6
Date7
Date8
Date9
Date10
Total Shares Paid

Share 1 (purchase 1) = Date1 (Purchase date 1)

 

[Peter_SSs]

1. Please confirm they are column labels, not row labels?

2. The first column heading (Number) seems to have disappeared so the results will only be 4 columns (First Name, Last Name, Amount, Date) or will all the left hand columns need to be repeated?

3. Is this just a one-off job?

4. If the answer to 3 is 'No', is the number of 'Share'/'Date' columns always the same (10 in your current example)?

5. If the anwer to 4 is 'No', ..
- do the 'Share' columns always start in a particular column (eg col AC)?
- are the 'Date' columns always followed by just one other column?

 

[hiker95]

jmh2008

I so wish I could work VBA!!!

Training / Books / Sites:

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.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

http://www.youtube.com/user/ExcelIsFun#g/search

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

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.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA, by John Walkenbach

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…
http://www.mrexcel.com/learnexcel2.shtml

DonkeyOte: My Recommended Reading:
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

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

Introduction to Array Formulas
http://www.xtremevbtalk.com/showthread.php?t=296012

Using Pivot Tables and Pivot Charts in Microsoft Excel
http://peltiertech.com/Excel/Pivots/pivotstart.htm

A List of Pivot Table links at Jon Peltier's site (contributed by Debra Dalgleish) is here:
http://peltiertech.com/Excel/Pivots/pivotlinks.htm

Email from XL - VBA & Outlook VBA
http://www.rondebruin.nl/sendmail.htm
http://www.outlookcode.com/article.aspx?ID=40

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

Excel 2007 function name translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Basics of array formulas
http://www.youtube.com/view_play_lis...7E7E9CA63304D3

Array formula data extract formulas
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Unique Record Counting and Data Extract formulas
http://www.youtube.com/watch?v=uUrI8hoj8BA