Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Copy cell contents to new cells automaticly

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Germany
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,
    I have no experience with Excel, but I have to use it

    I use a spreadsheet with 100 rows. Looks like this:

    AAA | W | X | Y | Z
    ----------------------------
    ABC | 5 | 8 | 11 | 4
    DEF | 0 | 2 | 4 | 10
    GHI | 3 | 68 | 7 | 34

    My input (can be copied into Excel) looks like this:

    -----------------------------
    ABC | W | 5
    ABC | X | 8
    ABC | Y | 11
    ABC | Z | 4
    DEF | W | 0
    DEF | X | 2
    DEF | Y | 4
    DEF | Z | 10
    GHI | W | 3
    GHI | X | 68
    GHI | Y | 7
    GHI | Z | 34

    The spreadsheet is growing and now I want to save time and look for a possiblity to make the updates easier.

    The macro or whatever shoud look to the input and do the following: ABC, value for W is 5, and update the correct cell on my spreadsheet and so on, until the last row of the input is reached.

    Can someone give me some instruction how to do?


    [ This Message was edited by: enne on 2002-05-06 06:52 ]

    [ This Message was edited by: enne on 2002-05-06 06:54 ]

    [ This Message was edited by: enne on 2002-05-06 06:57 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    USING EQUATIONS:

    STEP 1:
    Place your source text anywhere in the same workbook, as you showed.

    STEP2:
    Select the upper left corner cell of data (ABC in your example, i think) in your source table. Then select Insert Menu > Name > Define > Enter ULC (as the name) > Click ADD {This has just named that cell so that you can refer to it in your equation).

    STEP 3:
    Create the following new table {A B C and 1 2 3 4 5 6 7 are to represent the column and row headings, not things to enter.}

    A B C D
    1 ROW COL NAME DATA
    2 0 1 =offset(ULC,$A2,0) =offset(ULC,$A2,$B2)
    3 0 2
    4 0 3
    5 0 4
    6 =$A2+1 =$B2 =offset(ULC,$A6,0) =offset(ULC,$A6,$B6)

    The function "=offset(ULC, 1,2)" is saying to excel "Go the cell named ULC; Then go down 1 row, and right 2 columns; and tell me what number is in that cell." See MS excel help.

    STEP 3: Drag down the formulas (click the cell with the formula, then hover the mouse over the black square in the lower left corner until the cursor becomes a black cross. Then click and drag down the formular as far as you need. {Or select the entire range you want, then use Menu Edit > Fill > Down}

    STEP 4: Check the equations For example, row # 7 will be:

    7 =$A3+1 =$B3 =offset(ULC,$A7,0) =offset(ULC,$A7,$B7)

    Good Luck,

    Brian



  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's try that again, using _ to space out the text:

    _____A__________B___________C___________D
    1____ROW_______COL________NAME________DATA
    2_____0_________1_____=offset(ULC,$A2,0)___=offset(ULC,$A2,$B2)_
    3_____0_________2_
    4_____0_________3
    5_____0_________4
    6____=$A2+1___=$B2_____=offset(ULC,$A6,0)___=offset(ULC,$A6,$B6)_


    Col A ___ col B should look like:
    0__1
    0__2
    0__3
    0__4
    1__1
    1__2
    1__3
    1__4
    2__1
    2__2
    2__3
    2__4
    3__1

    This assumes that you have 4 columns of data. Adjust accordingly.

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Germany
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian,
    thanks for your reply. I will try it.

    Stephan

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Germany
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian,

    sorry, I forgot to say, that the input is ordered alphabetic in the first column and numeric in the second column.

    But the result in my spreadsheet is a unordered list (the list has a order, but not the same as the input and can't be changed.)

    The list should look like this:

    AAA | W | X | Y | Z
    ----------------------------
    GHI | 3 | 68 | 7 | 34
    DEF | 0 | 2 | 4 | 10
    ABC | 5 | 8 | 11 | 4

    Therefore I need equations, or something different performing a function like this:
    goto worksheet input, search for GHI in column 1 and for W in column 2 and return the value of column 3 in this row.

    Is this possible?

    Thanks, Stephan

  6. #6
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi enne. If your spreadsheet is like this,please copy this into a standard module and run "Test".
    Microsoft Excel - Copy cell contents to new cells automaticly.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=AAA
    ABCDE
    1AAA W X Y Z
    2GHI 368734
    3DEF 02410
    4ABC 58114
    input





    Sub Test()
    Dim rng As Range, lngRow As Long, intCol As Integer
    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    On Error Resume Next
    Sheets("4Test").Delete
    On Error GoTo 0
    Sheets.Add.Name = "4Test"
    With Sheets("input")
    For Each rng In Range(.[A2], .[A65536].End(xlUp))
    For intCol = 1 To 4
    lngRow = lngRow + 1
    rng.Copy Sheets("4Test").Cells(lngRow, 1)
    Union(rng.Offset(, intCol), .[A1].Offset(, intCol)).Copy
    Sheets("4Test").Cells(lngRow, 2).PasteSpecial , Transpose:=True
    Next
    Next
    End With
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
    End With
    End Sub


    _________________
    With regards,
    Colo




    [ This Message was edited by: Colo on 2002-05-07 23:03 ]

  7. #7
    New Member
    Join Date
    May 2002
    Location
    Germany
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Colo,

    thanks for your answer. Unfortunately I don't get the result. I try again to explain and be more specific with the input and output.

    My worksheet called 'overview' looks like this:
    __A______B___C_____D______E______F______G
    1_
    2_Name___________Data1__Data2__Data3__Data4
    3_
    4_Bill
    5_Hugo
    6_John

    The data to be filled in is from D4 to G6.

    My input worksheed called 'input' looks like this:

    ___A______B______C______
    1__Name__Check__Problem
    2__Hugo__Data1___5
    3__Hugo__Data2___8
    4__Hugo__Data3___11
    5__Hugo__Data4___4
    6__John__Data1___0
    7__John__Data2___4
    8__John__Data3___2
    9__John__Data4___10
    10_Bill__Data1___3
    11_Bill__Data2___68
    12_Bill__Data3___7
    13_Bill__Data4___34

    The data in the 'overview' worksheet is around 100 rows and growing. New Names will be added at the end of the list. The input has a different sort order, therefore I need the following:

    Cell D4 on the 'overview' sheet needs the value from cell C10 out of the 'input' sheet.

    That means, the macro or equations for cell D4 should look for the value from the problem column for Bill and Data1.

    Row 2 and column 2 and 3 are used for other data in the 'overview' sheet.

    Thank you very much.


    [ This Message was edited by: enne on 2002-05-09 02:09 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    I try again to explain and be more specific with the input and output.

    My worksheet called 'overview' looks like this:
    __A______B___C_____D______E______F______G
    1_
    2_Name___________Data1__Data2__Data3__Data4
    3_
    4_Bill
    5_Hugo
    6_John

    The data to be filled in is from D4 to G6.

    My input worksheed called 'input' looks like this:

    ___A______B______C______
    1__Name__Check__Problem
    2__Hugo__Data1___5
    3__Hugo__Data2___8
    4__Hugo__Data3___11
    5__Hugo__Data4___4
    6__John__Data1___0
    7__John__Data2___4
    8__John__Data3___2
    9__John__Data4___10
    10_Bill__Data1___3
    11_Bill__Data2___68
    12_Bill__Data3___7
    13_Bill__Data4___34

    The data in the 'overview' worksheet is around 100 rows and growing. New Names will be added at the end of the list. The input has a different sort order, therefore I need the following:

    Cell D4 on the 'overview' sheet needs the value from cell C10 out of the 'input' sheet.

    That means, the macro or equations for cell D4 should look for the value from the problem column for Bill and Data1.

    Row 2 and column 2 and 3 are used for other data in the 'overview' sheet.


    I'll assume that A1:C14 in sheet Input houses the sample you provided:

    {"Name","Check","Problem ";
    "Hugo","Data1",5;
    "Hugo","Data2",8;
    "Hugo","Data3",11;
    "Hugo","Data4",4;
    "John","Data1",0;
    "John","Data2",4;
    "John","Data3",2;
    "John","Data4",10;
    "Bill","Data1",3;
    "Bill","Data2",68;
    "Bill","Data3",7;
    "Bill","Data4",34}

    and the sample is sorted on column A.

    Activate Insert|Name|Define.
    Enter Nrecs as name in the Names in workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Input!$C:$C)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Drecs as name in the Names in workbook box.
    Enter as formula in the Refers to box:

    =Nrecs-(ROW(Input!$A$2)-1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter NAMES as name in the Names in workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Input!$A$2,0,0,Drecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter CHECKS as name in the Names in workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Input!$B$2,0,0,Drecs,1)

    Activate OK.

    I'll assume that A2:E5 houses the following in sheet Overview:

    {"Name","Data1","Data2","Data3","Data4";
    "Bill","","","","";
    "Hugo","","","","";
    "John","","","",""}

    where "" stands for an empty cell.

    In B1:E1 enter: {1,2,3,4}

    In B3 enter:

    =IF(LEN($A3),INDEX(CHECKS,MATCH($A3,NAMES,0)),"")

    In C3 enter:

    =IF(AND(LEN($A3),COUNTIF(NAMES,$A3)>=C$1),INDEX(CHECKS,MATCH($A3,NAMES,0)+C$1-1),"")

    Select B3:C3 and copy across first to E3 then down as far as needed.

    That's what you get to see in the results area in Overview:

    {"",1,2,3,4;"Name","Data1","Data2","Data3","Data4";
    "Bill","Data1","Data2","Data3","Data4";
    "Hugo","Data1","Data2","Data3","Data4";
    "John","Data1","Data2","Data3","Data4"}

    If in trouble implementing the foregoing, ask for the example workbook that shows all this.

    Aladin

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Germany
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin,

    thanks a lot, great job. It works perfect after a few changes. I had to replace the , in the formulas to a ; and changed the formula for the CHECKS to
    =OFFSET(Input!$B$2;0;1;Drecs;1).

    Now is only a small problem left.

    If the input has for example for John only 2 rows of data (Data3 and Data4), than the value of Data3 is put into the Data1 field and the value for Data4 into Data2 field. Data3 and Data4 is blank.

    Is there a chance to get this adjusted.
    If not, than I have only a little bit manual work. Better than before.

    Again, many thanks!!!
    Stephan

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    I had to replace the , in the formulas to a ; and changed the formula for the CHECKS to
    =OFFSET(Input!$B$2;0;1;Drecs;1).


    Yes, the list separator "," must be changed to ";" in European language versions of Excel.

    Now is only a small problem left.

    If the input has for example for John only 2 rows of data (Data3 and Data4), than the value of Data3 is put into the Data1 field and the value for Data4 into Data2 field. Data3 and Data4 is blank.

    Is there a chance to get this adjusted.
    If not, than I have only a little bit manual work. Better than before.



    I took those Data1,... to be just data like numbers or names. But, it seems to be things whose numbering has something significant?

    Care to elaborate what that is? I'm asking this because the formulas as they are now treat them just ordinary data. If Data1 has to be recognized as Data1, the formulas need to be modified. So what are they?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •