Converting Header Rows to Add'l Detail in Rows

I am working with a spreadsheet created from our mainframe and need some 
assistance in converting it to a file easily used for data mining.  The 
records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for 
each header row, 3=sum count of the # of detailed records.  Here is a sample 
of the data:

A    B       C           D                 E              F
1    Test   Texas    PlanNumber   RedLight
2    John   Addy     State             ZipCode     Expense
2    Sally   Addy     State             ZipCode     Expense
2    Jake   Addy      State            ZipCode     Expense
2    Hank  Addy      State            ZipCode     Expense
3    4
1   Test    Okl        PlanNumber   YellowLight
2    Lily     Addy     State             ZipCode    Expense
2    Deb    Addy     State             ZipCode    Expense
2    Joe    Addy      State             ZipCode    Expense
3    3

Here's the output I need:
A    B       C           D               E            F    G       H         
I         J            K
1    Test   Texas  PlanNumber  RedLight  2    John   Addy   State  ZipCode  
Expense
1    Test   Texas  PlanNumber  RedLight  2    Sally   Addy   State ZipCode  
Expense
1    Test   Texas  PlanNumber  RedLight  2    Jake   Addy    State ZipCode  
Expense
1    Test   Texas  PlanNumber  RedLight  2    Hank  Addy    State ZipCode  
Expense
3    4

Any help would be GREATLY appreciated!  I have 50 weekly files throughout 
2009 with over 20k records each!
0
Utf
12/12/2009 6:02:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1000 Views

Similar Articles

[PageSpeed] 19

Give this macro a try (change the assignments in the two Const statements to 
match your actual conditions)...

Sub ConsolidateDataRows()
  Dim X As Long, FirstAddressRow As Long
  Dim Rng As Range, One As Range, Three As Range
  Const StartRow As Long = 1
  Const SheetName As String = "Sheet1"
  With Worksheets(SheetName)
    Set Rng = .Range("A" & StartRow & ":A" & Rows.Count)
    Set One = Rng.Find("1", After:=.Cells(Rows.Count, "A"), _
                       LookIn:=xlValues, LookAt:=xlWhole, _
                       SearchDirection:=xlNext)
    If Not One Is Nothing Then
      FirstAddressRow = One.Row
      Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
                           LookAt:=xlWhole, SearchDirection:=xlNext)
      Do
        With Range(One.Offset(1), Three.Offset(-1)).Resize(, 6)
          .Copy One.Offset(1, 5)
          .Resize(, .Columns.Count - 1).Value = One.Resize(, 5).Value
          One.EntireRow.Delete
        End With
        Set One = Rng.Find("1", After:=Three, LookIn:=xlValues, _
                           LookAt:=xlWhole, SearchDirection:=xlNext)
        Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
                             LookAt:=xlWhole, SearchDirection:=xlNext)
      Loop While One.Row > FirstAddressRow
    End If
  End With
End Sub

-- 
Rick (MVP - Excel)


"krisfj40" <krisfj40@discussions.microsoft.com> wrote in message 
news:CB5B356E-E6A3-4792-8433-48C7DA029C7E@microsoft.com...
>I am working with a spreadsheet created from our mainframe and need some
> assistance in converting it to a file easily used for data mining.  The
> records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for
> each header row, 3=sum count of the # of detailed records.  Here is a 
> sample
> of the data:
>
> A    B       C           D                 E              F
> 1    Test   Texas    PlanNumber   RedLight
> 2    John   Addy     State             ZipCode     Expense
> 2    Sally   Addy     State             ZipCode     Expense
> 2    Jake   Addy      State            ZipCode     Expense
> 2    Hank  Addy      State            ZipCode     Expense
> 3    4
> 1   Test    Okl        PlanNumber   YellowLight
> 2    Lily     Addy     State             ZipCode    Expense
> 2    Deb    Addy     State             ZipCode    Expense
> 2    Joe    Addy      State             ZipCode    Expense
> 3    3
>
> Here's the output I need:
> A    B       C           D               E            F    G       H
> I         J            K
> 1    Test   Texas  PlanNumber  RedLight  2    John   Addy   State  ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Sally   Addy   State ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Jake   Addy    State ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Hank  Addy    State ZipCode
> Expense
> 3    4
>
> Any help would be GREATLY appreciated!  I have 50 weekly files throughout
> 2009 with over 20k records each! 

0
Rick
12/12/2009 8:32:35 PM
Sub test()
     Const cFirstRow = 1
     Dim i As Long
     Dim strItemName As String, strState As String, strPlan As String, strLight As String
     Dim rngDest As Range

     Set rngDest = Sheet2.Cells(1, 1)

     With Sheet1
         For i = cFirstRow To .Cells(Rows.Count, 1).End(xlUp).Row
             If .Cells(i, 1) = 1 Then
                 strItemName = .Cells(i, 2)
                 strState = .Cells(i, 3)
                 strPlan = .Cells(i, 4)
                 strLight = .Cells(i, 5)

             ElseIf .Cells(i, 1) = 2 Then
                 rngDest = 1
                 rngDest.Offset(0, 1) = strItemName
                 rngDest.Offset(0, 2) = strState
                 rngDest.Offset(0, 3) = strPlan
                 rngDest.Offset(0, 4) = strLight
                 rngDest.Offset(0, 5) = .Cells(i, 1)
                 rngDest.Offset(0, 6) = .Cells(i, 2)
                 rngDest.Offset(0, 7) = .Cells(i, 3)
                 rngDest.Offset(0, 8) = .Cells(i, 4)
                 rngDest.Offset(0, 9) = .Cells(i, 5)
                 rngDest.Offset(0, 10) = .Cells(i, 6)

                 Set rngDest = rngDest.Offset(1, 0)

             ElseIf .Cells(i, 1) = 3 Then
                 rngDest = .Cells(i, 1)
                 rngDest.Offset(0, 1) = .Cells(i, 2)

                 Set rngDest = rngDest.Offset(1, 0)

             End If
         Next
     End With
End Sub


Cheers,
Rob


On 13-Dec-2009 07:02, krisfj40 wrote:
> I am working with a spreadsheet created from our mainframe and need some
> assistance in converting it to a file easily used for data mining.  The
> records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for
> each header row, 3=sum count of the # of detailed records.  Here is a sample
> of the data:
>
> A    B       C           D                 E              F
> 1    Test   Texas    PlanNumber   RedLight
> 2    John   Addy     State             ZipCode     Expense
> 2    Sally   Addy     State             ZipCode     Expense
> 2    Jake   Addy      State            ZipCode     Expense
> 2    Hank  Addy      State            ZipCode     Expense
> 3    4
> 1   Test    Okl        PlanNumber   YellowLight
> 2    Lily     Addy     State             ZipCode    Expense
> 2    Deb    Addy     State             ZipCode    Expense
> 2    Joe    Addy      State             ZipCode    Expense
> 3    3
>
> Here's the output I need:
> A    B       C           D               E            F    G       H
> I         J            K
> 1    Test   Texas  PlanNumber  RedLight  2    John   Addy   State  ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Sally   Addy   State ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Jake   Addy    State ZipCode
> Expense
> 1    Test   Texas  PlanNumber  RedLight  2    Hank  Addy    State ZipCode
> Expense
> 3    4
>
> Any help would be GREATLY appreciated!  I have 50 weekly files throughout
> 2009 with over 20k records each!
0
Rob
12/12/2009 8:54:33 PM
Rick,
Thank you!  This is "almost" working.  The reason I say almost is because my 
header record 1 has data in columns A, B, C, D, G, H, and I (notice E, F are 
blanks).  The data in record type 2 has data in columns A-H, J for every row 
and some of them also use column I (but not all).

The macro is only grabbing the header record data in columns A, B, C, and D.

As you have probably noticed, I am not a programmer, so your assistance is 
greatly appreciated!!

Kris
"Rick Rothstein" wrote:

> Give this macro a try (change the assignments in the two Const statements to 
> match your actual conditions)...
> 
> Sub ConsolidateDataRows()
>   Dim X As Long, FirstAddressRow As Long
>   Dim Rng As Range, One As Range, Three As Range
>   Const StartRow As Long = 1
>   Const SheetName As String = "Sheet1"
>   With Worksheets(SheetName)
>     Set Rng = .Range("A" & StartRow & ":A" & Rows.Count)
>     Set One = Rng.Find("1", After:=.Cells(Rows.Count, "A"), _
>                        LookIn:=xlValues, LookAt:=xlWhole, _
>                        SearchDirection:=xlNext)
>     If Not One Is Nothing Then
>       FirstAddressRow = One.Row
>       Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
>                            LookAt:=xlWhole, SearchDirection:=xlNext)
>       Do
>         With Range(One.Offset(1), Three.Offset(-1)).Resize(, 6)
>           .Copy One.Offset(1, 5)
>           .Resize(, .Columns.Count - 1).Value = One.Resize(, 5).Value
>           One.EntireRow.Delete
>         End With
>         Set One = Rng.Find("1", After:=Three, LookIn:=xlValues, _
>                            LookAt:=xlWhole, SearchDirection:=xlNext)
>         Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
>                              LookAt:=xlWhole, SearchDirection:=xlNext)
>       Loop While One.Row > FirstAddressRow
>     End If
>   End With
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "krisfj40" <krisfj40@discussions.microsoft.com> wrote in message 
> news:CB5B356E-E6A3-4792-8433-48C7DA029C7E@microsoft.com...
> >I am working with a spreadsheet created from our mainframe and need some
> > assistance in converting it to a file easily used for data mining.  The
> > records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for
> > each header row, 3=sum count of the # of detailed records.  Here is a 
> > sample
> > of the data:
> >
> > A    B       C           D                 E              F
> > 1    Test   Texas    PlanNumber   RedLight
> > 2    John   Addy     State             ZipCode     Expense
> > 2    Sally   Addy     State             ZipCode     Expense
> > 2    Jake   Addy      State            ZipCode     Expense
> > 2    Hank  Addy      State            ZipCode     Expense
> > 3    4
> > 1   Test    Okl        PlanNumber   YellowLight
> > 2    Lily     Addy     State             ZipCode    Expense
> > 2    Deb    Addy     State             ZipCode    Expense
> > 2    Joe    Addy      State             ZipCode    Expense
> > 3    3
> >
> > Here's the output I need:
> > A    B       C           D               E            F    G       H
> > I         J            K
> > 1    Test   Texas  PlanNumber  RedLight  2    John   Addy   State  ZipCode
> > Expense
> > 1    Test   Texas  PlanNumber  RedLight  2    Sally   Addy   State ZipCode
> > Expense
> > 1    Test   Texas  PlanNumber  RedLight  2    Jake   Addy    State ZipCode
> > Expense
> > 1    Test   Texas  PlanNumber  RedLight  2    Hank  Addy    State ZipCode
> > Expense
> > 3    4
> >
> > Any help would be GREATLY appreciated!  I have 50 weekly files throughout
> > 2009 with over 20k records each! 
> 
> .
> 
0
Utf
12/12/2009 10:45:02 PM
I figured it out and it seems to be working.  Thank you!  Here is the macro I 
used...

Sub ConsolidateDataRows()
  Dim X As Long, FirstAddressRow As Long
  Dim Rng As Range, One As Range, Three As Range
  Const StartRow As Long = 1
  Const SheetName As String = "Sheet1"
  With Worksheets(SheetName)
    Set Rng = .Range("A" & StartRow & ":A" & Rows.Count)
    Set One = Rng.Find("1", After:=.Cells(Rows.Count, "A"), _
                       LookIn:=xlValues, LookAt:=xlPart, _
                       SearchDirection:=xlNext)
    If Not One Is Nothing Then
      FirstAddressRow = One.Row
      Set Twelve = Rng.Find("3", After:=One, LookIn:=xlValues, _
                           LookAt:=xlPart, SearchDirection:=xlNext)
      Do
        With Range(One.Offset(1), Twelve.Offset(-1)).Resize(, 10)
          .Copy One.Offset(1, 9)
          .Resize(, .Columns.Count - 1).Value = One.Resize(, 9).Value
          One.EntireRow.Delete
        End With
        Set One = Rng.Find("1", After:=Twelve, LookIn:=xlValues, _
                           LookAt:=xlPart, SearchDirection:=xlNext)
        Set Twelve = Rng.Find("3", After:=One, LookIn:=xlValues, _
                             LookAt:=xlPart, SearchDirection:=xlNext)
      Loop While One.Row > FirstAddressRow
    End If
  End With
End Sub

"krisfj40" wrote:

> Rick,
> Thank you!  This is "almost" working.  The reason I say almost is because my 
> header record 1 has data in columns A, B, C, D, G, H, and I (notice E, F are 
> blanks).  The data in record type 2 has data in columns A-H, J for every row 
> and some of them also use column I (but not all).
> 
> The macro is only grabbing the header record data in columns A, B, C, and D.
> 
> As you have probably noticed, I am not a programmer, so your assistance is 
> greatly appreciated!!
> 
> Kris
> "Rick Rothstein" wrote:
> 
> > Give this macro a try (change the assignments in the two Const statements to 
> > match your actual conditions)...
> > 
> > Sub ConsolidateDataRows()
> >   Dim X As Long, FirstAddressRow As Long
> >   Dim Rng As Range, One As Range, Three As Range
> >   Const StartRow As Long = 1
> >   Const SheetName As String = "Sheet1"
> >   With Worksheets(SheetName)
> >     Set Rng = .Range("A" & StartRow & ":A" & Rows.Count)
> >     Set One = Rng.Find("1", After:=.Cells(Rows.Count, "A"), _
> >                        LookIn:=xlValues, LookAt:=xlWhole, _
> >                        SearchDirection:=xlNext)
> >     If Not One Is Nothing Then
> >       FirstAddressRow = One.Row
> >       Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
> >                            LookAt:=xlWhole, SearchDirection:=xlNext)
> >       Do
> >         With Range(One.Offset(1), Three.Offset(-1)).Resize(, 6)
> >           .Copy One.Offset(1, 5)
> >           .Resize(, .Columns.Count - 1).Value = One.Resize(, 5).Value
> >           One.EntireRow.Delete
> >         End With
> >         Set One = Rng.Find("1", After:=Three, LookIn:=xlValues, _
> >                            LookAt:=xlWhole, SearchDirection:=xlNext)
> >         Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
> >                              LookAt:=xlWhole, SearchDirection:=xlNext)
> >       Loop While One.Row > FirstAddressRow
> >     End If
> >   End With
> > End Sub
> > 
> > -- 
> > Rick (MVP - Excel)
> > 
> > 
> > "krisfj40" <krisfj40@discussions.microsoft.com> wrote in message 
> > news:CB5B356E-E6A3-4792-8433-48C7DA029C7E@microsoft.com...
> > >I am working with a spreadsheet created from our mainframe and need some
> > > assistance in converting it to a file easily used for data mining.  The
> > > records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for
> > > each header row, 3=sum count of the # of detailed records.  Here is a 
> > > sample
> > > of the data:
> > >
> > > A    B       C           D                 E              F
> > > 1    Test   Texas    PlanNumber   RedLight
> > > 2    John   Addy     State             ZipCode     Expense
> > > 2    Sally   Addy     State             ZipCode     Expense
> > > 2    Jake   Addy      State            ZipCode     Expense
> > > 2    Hank  Addy      State            ZipCode     Expense
> > > 3    4
> > > 1   Test    Okl        PlanNumber   YellowLight
> > > 2    Lily     Addy     State             ZipCode    Expense
> > > 2    Deb    Addy     State             ZipCode    Expense
> > > 2    Joe    Addy      State             ZipCode    Expense
> > > 3    3
> > >
> > > Here's the output I need:
> > > A    B       C           D               E            F    G       H
> > > I         J            K
> > > 1    Test   Texas  PlanNumber  RedLight  2    John   Addy   State  ZipCode
> > > Expense
> > > 1    Test   Texas  PlanNumber  RedLight  2    Sally   Addy   State ZipCode
> > > Expense
> > > 1    Test   Texas  PlanNumber  RedLight  2    Jake   Addy    State ZipCode
> > > Expense
> > > 1    Test   Texas  PlanNumber  RedLight  2    Hank  Addy    State ZipCode
> > > Expense
> > > 3    4
> > >
> > > Any help would be GREATLY appreciated!  I have 50 weekly files throughout
> > > 2009 with over 20k records each! 
> > 
> > .
> > 
0
Utf
12/12/2009 11:31:01 PM
Reply:

Similar Artilces:

Printing Headers
Correct me if I'm wrong, but in Outlook 97 I used to be able to print an "Unsent" message and it would included the header information and subject line. But now in Outlook 2002 when I go to print a message in Draft form it doesn't print the Header information regardless of whether the message is in Plain Text, RTF or HTML format? Is this true and if so, is there a work-around? ...

delete empy rows
Hi, I have a sheet with 3000 lines, I am using only thre coomns A, B, C How can I delete all rows that has empty cells in colomn C i.e. row 5 contains Sam in colomn A , 2 in colomns B , nothing in colomn C. I want to delete this row row 6 contains 3 in colomn A, nothing in colomns B, 34 in colomn C. I want to keep this row. can this be done Khalil Try this on a *spare* copy .. Select col C Press F5 > Special > Blanks > OK Right-click on the selection > Delete > Entire row > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>y...

Unhiding rows
In Excel 2000, someone has hid the first 45 rows of the worksheet. I can't unhide them. The sheet is NOT protected. I tried various things including Edit/Go to/A1/Format/Row/Unhide but nothing works. Any clues? .......Baffled in Ontario row height? -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Connie" <marverncon@hotmail.com> wrote in message news:0b4001c35c24$077545d0$a601280a@phx.gbl... > In Excel 2000, someone has hid the first 45 rows of the > worksheet. I can't unhide them. The sheet is NOT > protected. I t...

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

Headers & Footers with Ampersands
I have a footer I'm trying to add to my document. When I enter ST&T (my acronym) what ends up being displayed is ST&[Time] and then the time is shown on my documents. I've tried using a double ampersand like excel requires and as in older version of project, to no avail. Ultimately I end up not using the ampersand. How can I input my acronym correctly and not have the time be displayed on my documents where it should be. I am using MS Project 2007 Standard. Thank You, Sue Hi Sue, the way Mike described in your earlier post should work. I have no S...

select and delete all blank rows
I could save a lot of time if I knew how to select and delete all blank rows from a document, and also separate things. An example of separating things would be a list contains fruits, vegetables and meat. I could separate all the fruits from the list easily. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then clic...

files & icons always in "details" type view
Hello: I've created some folders in the desktop, and put inside shortcuts to programs that I use mostly. Although I always choose view --> icons, at each startup shortcuts re-appears in "details" view mode. I checked in "folder options", "Remember each folder's view settings" is checked, so what's the problem? thank you Maurice ...

65536 Rows for how long????
Hi there! I was wondering: is Microsoft planning on expanding the 65536 row limit? They most deffinitely should! Best regards, Albert "Albert" <Albert@discussions.microsoft.com> wrote > I was wondering: is Microsoft planning on expanding the 65536 row limit? > They most deffinitely should! Why "certainly"? -- Bob http://www.kanyak.com Well, not "certainly", but its certainly my opinion. I think 65536 is not enough. I believe that, among other things, Excel was made for managing large amounts of information efficiently. I think that in this d...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....

Rows to Columns
I have data arranged in rows across a spread sheet, for example B2, C2, D2, etc. How can I change that to a column, for example A1, A2, A3, etc without reentering all the data? Thank you --Select B2:D2. Copy the range --Select A1. Right click>PasteSpecial>Transpose>OK If this post helps click Yes --------------- Jacob Skaria "Walter Seaton" wrote: > I have data arranged in rows across a spread sheet, for example B2, C2, D2, > etc. > How can I change that to a column, for example A1, A2, A3, etc without > reentering all the data? >...

Where are internet headers in outlook 2010
In Outlook 2010 where did the Internet Header go? Message Options? In 2007 you could right click on any message in your inbox and see the Internet headers. IP address message info ect.... Thanks Nevermind. I found it. You have to actually open up the email, then go to "View" then Message options. It is only a menu item now. Hope this helps someone using Outlook 2010 "Brian" wrote: > In Outlook 2010 where did the Internet Header go? Message Options? > > In 2007 you could right click on any message in your inbox and see the > Intern...

Viewing schedule details
We have upgraded from Novell Groupwise to Exchange and Outlook. I have figured out how to look at other's schedules, but noticed that details about the scheduled time is not currently available. For instance if I place in my calender an item that states that I am away in California on business Monday and Tuesday, when someone looks at my schedule all they'll see is that I am out of the office. Is there a way, through Exchange rights or whatever, to allow people to see the detail for a calendar item? Thanks for the info! ...

Converting Outlook Files
I need to convert all my files, (address book, saved messages, calendar etc.) from a computer running XP to a syster running 2000 Professional. If you are using Microsoft Outlook, you just need to copy over your *.pst file(s). From there, you can import the items into your new system or open the *.pst file via File | Open | Personal Folders/Outlook Data file and then drag/drop items between the old folder set and new one. If you are using Outlook Express, you should ensure that Windows 2000 is running IE6/6SP1 (which would be equal to Windows XP/Windows XP SP1 respectively). You will nee...

Filtering rows with tracked changes
Hi I am trying to work out how to filter the rows that have been updated over a period of a couple of days. I understand that you can highlight records with tracked changes in a certain time period � but I would like to be able to just have those rows that have highlighted cells to look at. Any ideas out there? You can list changes on a new sheet but it doesn't allow you to see the whole row. Cheers, Mike P.s � is it possible to highlight changes between dates � I can only see highlighting changes since � ...

How to convert closed invoice to active invoice?
maybye anyone has ideas how to solve this problem? ...

Mail header
Until few days ago when I received messages in which the "from" field, in the e-mail header. looked like this From: "Luisa T.B. \(CIMPA\)" <cimpa@something.com> in outlook it displayed "Luisa T.B. (CIMPA)", but recently it just writes CIMPA. I thought it was Outlook, maybe some patch or something, but older e-mails still display correctly (Luisa T.B.). Allso I think it could be an Exchange issue, when I look at the mails in OWA, mails allso display incorrectly the "from" field from recently, older mails look fine. It sounds like somethin...

Photos not moving with rows when sorted!
Excel 2000. A worksheet with names of people, and various data about them. Each row is a record--the person's first name, last name, and various data, each column is a field. One column is labeled "Photo", and has a small photo of the person in it. I notice that when I sort the rows, by data in one of the columns, such as "first name", "last name", etc., the photos do not sort correctly with their rows. Why? Do photos just remember their place on the page, their cell position (E7 or whatever), and not the row they are in, are not considered part of the row...

Filter eliminating records without and detail records
I need a filter to be able to hide records that don't have any records in the detail form. My example is Member Master and Event Detail. If there is no data in the Event Detail, I need to hide that Member Master record. Change the Can Shrink property in the form to yes. "Jeannie" wrote: > I need a filter to be able to hide records that don't have any records in the > detail form. My example is Member Master and Event Detail. If there is no > data in the Event Detail, I need to hide that Member Master record. I think you have a main form bound to the [Memb...

Resetting default row height to allow display of wrapped text
I'm using Excel 2000 and XP Pro. I'm formatting a text page whereby users can input text into a column and I want the text to wrap so that whatever the length of their text we see it all. On a default page it works fine. However I tinkered with the row height for font purposes and now the text will wrap but the row won't expand to fit the whole text. How do I reset the row/sheet or whatever to get the display I want? Thanks -- Stilson Snow There is a limit to how much text will display within a cell and how much text displays in the formula bar. Goto excel help and enter...

Linking rows of data to another worksheet
Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

Picture in Header
I want to insert a picture in the header of a excell doc. Is this possible? You need Excel XP, Excel 2003 or Excel 2004 for this. Picture is in the File > Page setup menu, selection Header/Footer > Custom header. HTH. Best wishes Harald "Esrei" <Esrei@discussions.microsoft.com> skrev i melding news:3900EE7C-5D78-4511-9AA2-4286EB4FB8A4@microsoft.com... > I want to insert a picture in the header of a excell doc. Is this possible? If you don't have a any of the Excel versions that Harald mentioned, take a look at www.nwarwick.co.uk. In the Excel section there i...

Header Row in Excel
I have a spread sheet with many columns and rows and I would like for the First line of each column to remain visible as a user scrolls down the rows so that he/she will be able to see that that data represents by simply looking up(instead of scrooling all the way back up) to see that is the heading of that column. Any help would be appreciated. Larry Hughes Larry Click on A2, then on the top toolbar, Window > Freeze panes. HTH George Gee "Larry Hughes" <LarryHughes@discussions.microsoft.com> wrote in message news:0717C389-9E1C-4F87-9027-D603E7B6DA4F@microsoft.c...

need to input alternate blank rows in spreadsheet
Need to input alternate blank rows into imported spreadsheet. Currently has 24000 lines of data and would like to find a way of not having to manually input these blank rows! help! Also if this is possible will I be able to format row height at the same time? Hi, In a blank column, insert numbers from 1 to 24,000 I.e. if data is from A2:F24001, then in G2:G24001, enter 1 to 24000. Now copy G2:G24001 and paste in G24002. Thereafter you can sort the numbers in column G in ascending order. This will insert one blank row after each row. -- Regards, Ashish Mathur Mic...

column widths and row heights
based on 100% view, when u set the column width to say 12 units and the row height to, for example, 15... what is the actual dimensions in reality in mm. ie, how can i get the required number to ensure that row and colms are exactly 2 mm in width and height pat Column width and row heights defaults are not in inches or mm. The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. For ...

Excel converts text to formulas %@#$
I wish Microsoft stopped developing "helpful" programs. All their "auto" stuff leads to nothing but problems. Here is an example: I have to process data which come as CSV files and contain entries that look like multiplication e.g "3*4". Damn Excel can't handle these. Every chance it gets, it turns them into "=3*4" and displays "12". Any one knows how to kill this behaviour? Please share the knowledge. Henryk Birecki Henryk Birecki wrote... >I wish Microsoft stopped developing "helpful" programs. All their >"auto&q...