+ Reply to Thread
Results 1 to 12 of 12

[Help]How Data array change

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    korean
    MS-Off Ver
    Excel 2010
    Posts
    10

    [Help]How Data array change

    I want find way how to Data type array change
    Can you help me?

    Thanks and Sorry for my Bad English

    change.xlsx
    Change.xlsx file is example sheet

    its work on VBA
    Last edited by system0; 12-11-2013 at 05:04 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: [Help]How Data array change

    Please upload your sample workbook to the forum. Not all members are able (or willing) to download files from file-hosting sites.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-04-2013
    Location
    korean
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: [Help]How Data array change

    Quote Originally Posted by FDibbins View Post
    Please upload your sample workbook to the forum. Not all members are able (or willing) to download files from file-hosting sites.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Oh
    i coudnt find that and i find!
    thanks XD

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: [Help]How Data array change

    Perhaps you could explain how you arrived at the values you did?

    Yoy go from...
    E
    F
    G
    H
    I
    J
    K
    11
    source
    option1
    option2
    option3
    option4
    option5
    12
    13
    1
    4
    6
    10
    25
    14
    1
    a
    0
    1
    0
    1
    0


    to...
    N
    O
    P
    Q
    R
    S
    12
    changed
    option1
    value1
    option2
    value2
    13
    14
    1
    a
    4
    1
    10
    1
    15
    2
    b
    1
    10
    4
    1

  5. #5
    Registered User
    Join Date
    10-04-2013
    Location
    korean
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: [Help]How Data array change

    Quote Originally Posted by FDibbins View Post
    Perhaps you could explain how you arrived at the values you did?

    Yoy go from...
    E
    F
    G
    H
    I
    J
    K
    11
    source
    option1
    option2
    option3
    option4
    option5
    12
    13
    1
    4
    6
    10
    25
    14
    1
    a
    0
    1
    0
    1
    0


    to...
    N
    O
    P
    Q
    R
    S
    12
    changed
    option1
    value1
    option2
    value2
    13
    14
    1
    a
    4
    1
    10
    1
    15
    2
    b
    1
    10
    4
    1
    i typed manually but that is low data so i can
    but if to many filed have sheet is that cant
    How i can using on function i tested now like if or index or match but i dont know TT

  6. #6
    Registered User
    Join Date
    10-04-2013
    Location
    korean
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: [Help]How Data array change

    Quote Originally Posted by system0 View Post
    i typed manually but that is low data so i can
    but if to many filed have sheet is that cant
    How i can using on function i tested now like if or index or match but i dont know TT
    Sorry i missed its not function its VBA

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: [Help]How Data array change

    hi there. don't really understand the explanation, but this seems to give you what you want. 2 different array formulas in P14 & Q14
    =IFERROR(INDEX($G$13:$K$13,SMALL(IF($G14:$K14>0,COLUMN($G14:$K14)),COUNTIF($P$12:P$12,"option*"))-COLUMN($G14)+1),"")
    =IFERROR(INDEX($G14:$K14,SMALL(IF($G14:$K14>0,COLUMN($G14:$K14)),COUNTIF($Q$12:Q$12,"value*"))-COLUMN($G14)+1),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    copy both formulas & paste formulas to the rest of the cells
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: [Help]How Data array change

    Thanks Ben, I was having a hard time with this 1

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: [Help]How Data array change

    Try this:

    Please Login or Register  to view this content.
    You'll of course have to adapt row / column references to suit your final solution, but you should get the principle.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: [Help]How Data array change

    On your example, if you delete P14 to Y19 and run this macro:
    Please Login or Register  to view this content.
    It will return the correct results that you typed in - maybe this helps?

  11. #11
    Registered User
    Join Date
    10-04-2013
    Location
    korean
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: [Help]How Data array change

    Quote Originally Posted by OllyXLS View Post
    Try this:

    Please Login or Register  to view this content.
    You'll of course have to adapt row / column references to suit your final solution, but you should get the principle.
    oh its tooooo helpful!
    thanks for Helping me! TT
    good day all member~~!
    and thanks for help all too

  12. #12
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: [Help]How Data array change

    @FDibbins:
    you're very welcome. but it looks like OP is looking for VBA code.

    @system0:
    if you're very sure you want VBA codes, you could post in the Excel Programming / VBA / Macros section in the future. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find Data in one array and match the position in another array
    By zeeman2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-27-2013, 07:55 PM
  2. If Condition is True, Save Data in Array, and Copy Array Data to other Worksheet
    By skelly8117 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2013, 10:18 AM
  3. How to write data into an array and retrieve data from array?
    By nigel_12 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-28-2013, 07:06 AM
  4. Please help me to change array id to new array id
    By quangnv in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-13-2012, 06:31 AM
  5. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1