1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Non-Adjacent Formula Filling - Columns vs Rows

Discussion in 'Business Applications' started by Stisfa, Apr 17, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    Okay, got a convoluted spreadsheet going on & I'm not entirely sure how to describe it in words. I tried several searches on these forums and on Google but I didn't find anything that was relevant. So I'm going to apologize in advance if I'm re-posting a topic due to my inability to use the appropriate vocabulary to describe this problem.

    Running:
    Windows XP Professional SP3
    Excel 2007

    I've attached an example spreadsheet.

    Here's the simplest way I can describe it:

    1) Test1 is the first spreadsheet I'm working with
    - All the data is already filled in by a vertical organization
    - This spreadsheet will be updated on a regular basis

    2) I'm trying to have the data in the columns moved over to another sheet
    - Horizontal organization (This can not be compromised - Absolutely necessary)
    - This is being done by formulas (of course)
    - This needs to be done on separate pages, i.e.: Jack should have his own page, Jill should have her own page
    - It would be preferable to find a way to use the Fill command, as there are more records currently and more coming (or at least a way to automate the proper filling)

    3) Is there any way in which this can be done with merged cells? If not, then I don't mind doing it with un-merged cells
    - Merged Cell Version is "Test2" Sheet
    - Un-Merged Cell Version is "Test3" Sheet

    Both the Vertical & Horizontal Based Spreadsheets are going to be updated on a regular basis but the first sheet is always going to take precedence (basically, the 2nd sheet is going to be working off of the data in the first spreadsheet)
     

    Attached Files:

  2. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    After reviewing my post, I realized I was a little too ambiguous...

    Well, I'll break it down in to several smaller problems, beginning with the most crucial one first.

    When I use the Fill Handle to go towards the right, the following formulas ensue:
    Original:
    =Test1!A2
    After Fill Handle (Right):
    =Test1!B2
    What I Want The Fill Function to Actually Do:
    =Test1!A3

    You'll see if you attempt it in the Spreadsheet itself:
    1) Go to the "Test3" Work Sheet
    2) Highlight B2:B4
    3) Drag the Fill Handle to the Right & Drop onto Cells C1:C4
    4) Inside Cells C1:C4, Notice that Inside the Formula, the Column Letter (A,B,C,D...) Changes but Not the Row Number!

    Here's an example of what is actually happening but I don't want it to:
    Code:
    -------------A--------------------B----------------C----------D
    1|        Customer #   |     =Test1!A2    |   =Test1!B2   |
    2|    Customer Name    |     =Test1!B2    |   =Test1!C2   |
    3|   Customer Phone    |     =Test1!C2    |   =Test1!D2   |
    4| Customer Address    |     =Test1!D2    |   =Test1!E2   |
    5|
    
    This is what I want it to look like after I use the Fill Handle:
    Code:
    -------------A--------------------B----------------C----------D
    1|        Customer #   |     =Test1!A2    |   =Test1!A3   |
    2|    Customer Name    |     =Test1!B2    |   =Test1!B3   |
    3|   Customer Phone    |     =Test1!C2    |   =Test1!C3   |
    4| Customer Address    |     =Test1!D2    |   =Test1!D3   |
    5|
    
    Again, apologies if this is something that's already been posted on; I'm just not sure what search terms would fit this scenario?
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi. I don't have 2007 at the office so I'll take a look at it when I get home.
    Just to get the idea:

    The list in Test1 is your source,
    and what you want is that the first row is transposed to Test2 as column
    and then row is transposed as the next column in Test 2

    I don't understand what you mean with 'Fill Handle' but if this is the idea I think it can be done, at least a macro will do the job.
    I'll do it at home and send you a test.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Just try this, it's quick and dirty. It will clear the contents of Sheet Test3 ans use Test1 as source

    Sub Transp001()
    '
    ' Transp001 Macro
    ' Recorde and editted by Hans Hallebeek 21-Apr-2010

    Set ws1 = Sheets("Test1")
    Set ws3 = Sheets("Test3")
    ws3.Cells.ClearContents
    xRow = 1
    xcol = 1
    Do While ws1.Cells(xRow, 1).Value <> ""
    ws1.Select
    Range(Cells(xRow, 1), Cells(xRow, 4)).Select
    Selection.Copy
    ws3.Select
    Cells(1, xcol).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    If xRow = 1 Then
    Selection.HorizontalAlignment = xlRight
    Else
    Selection.HorizontalAlignment = xlLeft
    End If
    Selection.EntireColumn.AutoFit
    Application.CutCopyMode = False
    xRow = xRow + 1
    xcol = xcol + 1
    Loop

    End Sub

    I hope this is the idea
     
  5. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    I haven't tried the macro yet but the short answer is: Yes (I haven't tried since I'm not sure how to use macros yet :D ... I won't bother you for help on that, I'll do some slow reading on it on my own)

    I need to transpose without using this methodology:
    Copy > Paste Special > Transpose

    Sorry for the confusion, here's a better explanation:

    Ideally, I want this:
    1) Test1 is the Source
    2) Row 2 from "Test1" should be transposed into Column C:H (merged) for Worksheet "Test2"
    3) I need the transposing function to replicate for each page afterward, so I need Columns I:J in "Test2" to contain the same contents in Columns A:B in "Test2", and so on and so forth (Columns Q:R, Y:Z, AG:AH...)
    4) Then I need the formulas in Columns C:H (merged) from Worksheet "Test2" to be sequentially progressive in Columns K:p (S:X, AA:AF, AI:AN)
    5) The sequence of the Formula should move in this horizontal manner (ignore the Column Letter Series & Column # Series below, they're just for illustrative purposes)

    Code:
    ---------A---------B-----------C-----------D-----------E-----------F------
    1| =Test1!A2 | =Test1!A3 | =Test1!A4 | =Test1!A5 | =Test1!A6 | =Test1!A7
    2| =Test1!B2 | =Test1!B3 | =Test1!B4 | =Test1!B5 | =Test1!B6 | =Test1!B7
    3| =Test1!C2 | =Test1!C3 | =Test1!C4 | =Test1!C5 | =Test1!C6 | =Test1!C7
    4| =Test1!D2 | =Test1!D3 | =Test1!D4 | =Test1!D5 | =Test1!D6 | =Test1!D7
    
    "Test3" Worksheet was designed to have No Merged Cells, in the event the transpose macro didn't like the Merged Cells in "Test2".

    I hope that makes more sense. If not, let me know if I need to explain it in a different way.

    By the by, thanks for the help!

    EDIT:
    I was doing a little research on macros, since I'm not sure how they work and found out about the Macro "Record" & "Run" functions; should I just try that instead of bothering you so much?
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    No problem, just ask, I don't know why you need merged cells but that's another question.
    I'm ám still not that comfortable with 2007 and don't like the riboon and all that
    I think I will have to do some learning myself
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    If you need this just for printing purposes then there is an easier way to do it, just take a look at the attached file.
     

    Attached Files:

  8. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    I see where you're going with the longer Column Lengths but I need to maintain separate cells beneath it so that I can structure tier-based notes.

    In Test3, the formula below had only returned the "The Customer # is to High" value in all the cells beneath the customer #...I was wondering what the intention of this formula was? I took a look at Test2 and found a different formula that worked great though (just that I need to keep merged cells, lol).

    =IF(B$1="","Input the Customer #",IF(B$1>MAX(Test1!$A:$A),"The Customer # is to High",VLOOKUP(B$1,Test1!$A$1:$G$30,MATCH($A2,Test1!$1:$1,0),FALSE)))

    I realize an RDBMS would be more suited to this kind of data entry but the scope & purpose of this little project wouldn't really merit hundreds of hours in learning a new program. Thanks for helping me mutilate Excel :D
     
  9. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    In test3 is almost the same formula, as in test2, I just added some stuff to it to show you the message / Result "The Customer # is to High" if the custom # is higher than the max Custom # on test1, and "Input the Customer #" if the field Custom# is null (has no value).
    If you will change the number in the Custom# you will see that the information in next rows will change too, and will correspond to the info in the Test1 associated to that Custom# .
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I think I missed something in between, I have a macro for you, I put it in my test sheet as attachment and it fills Test3
    The macro is named Transpose001

    I thought I had attached it in one of my previous answers, but I don't see it. Maybe it helps you.
     

    Attached Files:

  11. Stisfa

    Stisfa Thread Starter

    Joined:
    Nov 13, 2009
    Messages:
    72
    I ran the Macro and it's good, in principle.

    I just need to edit it to perform the cell formatting for me; I spent a couple hours trying to edit the macro to fit my specific needs but I failed. It's quite unfortunate that the closest I've ever come to programmings are Batch files & HTML, neither of which are actually programming :eek:.

    Point being, the macro more or less suits the need, I just need to edit it. I'll see what I can come up with and post it here.

    Thanks for the help Keebellah.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Don't hesitate to post your code and I'll help you.
    I love a programming challenge :)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917540

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice