Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA
Results 1 to 10 of 10

Thread: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Hello,

    I am trying to extract some data and place it in another part of the workbook, and sort it. Example:

    Column A has a title of "Cost" (A1), with the values being (A2:A6): $0, $0, $50.50, $0, $46

    Column B has a title of "Unique Number" (B1), with values of (B2:B6): 1, 2, 3, 4, 5

    Column C has a title of "Date" (C1), with the following values (C2:C6): 4/21, 5/11, 5/27, 6/4, 7/22


    I want to extract data from all three cells into a new table, but only the cells where the cost is greater than zero. And I want to the new table to be sorted from lowest cost to greatest cost. The final table should look like:

    Column L: $46, $50.50
    Column M: 5, 3
    Column N: 7/22, 5/27



    The key is I don't want to use VBA. Can anyone help? Thanks in advance!

  2. #2
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Hi and welcome to MrExcel.

    I'm sorry, but I cannot give you a Sorted solution, but will this help for now?...

    Sheet2

    *ABCDEFGHIJKLMNO
    1CostUnique NumberDate*******CriteriaCostUnique NumberDate*
    20121/04/2013*******050.5327/05/2013*
    30211/05/2013********46522/07/2013*
    450.5327/05/2013************
    50404/06/2013************
    646522/07/2013************
    7***************

    Spreadsheet Formulas
    CellFormula
    L2{=IF(ROWS(L$2:L2)>COUNTIF($A$2:$A$6,">"&$K$2),"",INDEX(A$2:A$6,SMALL(IF($A$2:$A$6>$K$2,ROW(A$2:A$6)),ROWS(A$2:A2))-ROW($A$2)+1))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    The formula in L2 needs to be entered with ctrl shift enter NOT just enter, it can then be copied across and down.
    You will obviously need to change the cell references to suit your layout.
    I'm sure there is a Sorted solution available, but I cannot think how to go about constructing it, either with Frequency or the aid of a "Helper" column, I guess. Or, you could just sort the data in your table by Cost, smallest to largest!!

    There may be some help here....

    excelisfun -- Excel How To Videos - YouTube

    Sort Values | Get Digital Help

    I'm out of time now, so good luck and I hope someone jumps in with the solution you require.

    Ak
    Last edited by Akashwani; Apr 18th, 2013 at 08:12 PM.
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  3. #3
    New Member
    Join Date
    Apr 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Hi Akashwani,

    Thank you for your post! That will definitely get me started and closer to my goal, but I will eventually need to have the cost sorted. Doing it manually is not an option since I will be using a solver function later.

    Do you know of a way to take the output you provided and sort the values into another set of columns (P, Q, & R)? There's plenty of room.

  4. #4
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Hi,

    Perhaps like this....

    Sheet2

     ABCDEFGHIJKLMNOPQRS
    1CostUnique NumberDate       CriteriaCostUnique NumberDate CostUnique NumberDate 
    250.5327/05/2013       050.5327/05/2013 46522/07/2013 
    346522/07/2013        46522/07/2013 50.5327/05/2013 
    40121/04/2013                
    50211/05/2013                
    60404/06/2013                
    7                   

    Spreadsheet Formulas
    CellFormula
    L2{=IF(ROWS(L$2:L2)>COUNTIF($A$2:$A$6,">"&$K$2),"",INDEX(A$2:A$6,SMALL(IF($A$2:$A$6>$K$2,ROW(A$2:A$6)),ROWS(A$2:A2))-ROW($A$2)+1))}
    P2=IFERROR(SMALL($L$2:$L$6,ROW(A1)),"")
    Q2=IFERROR(VLOOKUP($P2,$L$2:$N$3,COLUMN(B2),0),"")
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    The formula in L2 needs to be entered with ctrl shift enter NOT just enter, it can then be copied across and down.
    The formula in P2 needs to be copied down.
    The formula in Q2 needs to be coped across and down.
    You will obviously need to change the cell references to suit your layout.

    **I don't know how to provide a solution IF you have more than one number the same in column L**

    I hope that helps.

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Quote Originally Posted by FullMoonMadness View Post
    Hello,

    I am trying to extract some data and place it in another part of the workbook, and sort it. Example:

    Column A has a title of "Cost" (A1), with the values being (A2:A6): $0, $0, $50.50, $0, $46

    Column B has a title of "Unique Number" (B1), with values of (B2:B6): 1, 2, 3, 4, 5

    Column C has a title of "Date" (C1), with the following values (C2:C6): 4/21, 5/11, 5/27, 6/4, 7/22


    I want to extract data from all three cells into a new table, but only the cells where the cost is greater than zero. And I want to the new table to be sorted from lowest cost to greatest cost. The final table should look like:

    Column L: $46, $50.50
    Column M: 5, 3
    Column N: 7/22, 5/27



    The key is I don't want to use VBA. Can anyone help? Thanks in advance!
    A:C houses the data, L:N the processing...

    Cost Unique Number Date Cost Unique Number Date
    0 1 4/21 46 5 7/22
    0 2 5/21 46 7 7/23
    50.5 3 5/27 50 6 7/23
    0 4 6/4 50.5 3 5/27
    46 5 7/22
    50 6 7/23
    46 7 7/23

    L2, control+shift+enter, not just enter, and copy down:
    Code:
    =IFERROR(SMALL(IF($A$2:$A$8,$A$2:$A$8),ROWS($L$2:L2)),"")
    M2, control+shift+enter and copy down:
    Code:
    =IF(L2="","",INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=L2,
      ROW($A$2:$A$8)-ROW($A$2)+1),COUNTIF($L$2:L2,L2))))
    N2, just enter and copy down:
    Code:
    =IF(M2="","",INDEX($C$2:$C$8,MATCH(M2,$B$2:$B$8,0)))
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Hi Aladin.

    Thank you very much for dropping in and solving this.

    Thanks

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  7. #7
    New Member
    Join Date
    Apr 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Big thanks, Aladin! That did the job, though I later realized I needed the Cost column to sort in descending order (changed the SMALL function to LARGE).

    I have been butting my head against a similar issue with the same spreadsheet.

    Here it is:
    Now that I have my data sorted in columns L:N, I want to return the value from column M that meets the following criteria:
    1. Lowest cost where Cost (L2:L4) is greater than or equal to H2
    2. Date (N2:N4) is less than or equal to E2
    3. Unique Number (M2:M4) has not been used previously. I am keeping a table of used unique numbers in AC2:AS2.


    So, using the values in the spreadsheet above with the ones below:

    • H2=48
    • E2=8/10
    • AF2=6


    The result should be 3.

    I'm able to get parts one and two to work, but I'm stumped on three. Here's what I have done to make one and two work:
    INDEX($M$2:$M$4,MATCH($H4,IF($N$2:$N$4<=$E4,$L2:$L$4),-1))

    I've tried a number of things to make part three work, but have been striking out. Any ideas? Thanks!

  8. #8
    New Member
    Join Date
    Apr 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Sorry, it should be $H2 in the quote above, and I'm doing ctrl+shift+enter.

    INDEX($M$2:$M$4,MATCH($H2,IF($N$2:$N$4<=$E4,$L2:$L$4),-1))

  9. #9
    New Member
    Join Date
    Apr 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    *friendly bump*

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

    Quote Originally Posted by FullMoonMadness View Post
    Big thanks, Aladin! That did the job, though I later realized I needed the Cost column to sort in descending order (changed the SMALL function to LARGE).
    You are welcome.

    I have been butting my head against a similar issue with the same spreadsheet.

    Here it is:
    Now that I have my data sorted in columns L:N, I want to return the value from column M that meets the following criteria:
    1. Lowest cost where Cost (L2:L4) is greater than or equal to H2
    2. Date (N2:N4) is less than or equal to E2
    3. Unique Number (M2:M4) has not been used previously. I am keeping a table of used unique numbers in AC2:AS2.


    So, using the values in the spreadsheet above with the ones below:

    • H2=48
    • E2=8/10
    • AF2=6


    The result should be 3.

    I'm able to get parts one and two to work, but I'm stumped on three. Here's what I have done to make one and two work:



    I've tried a number of things to make part three work, but have been striking out. Any ideas? Thanks!
    Control+shift+enter, not just enter:
    Code:
    =INDEX($M$2:$M$5,MATCH(1,IF($L$2:$L$5>=H2,IF($N$2:$N$5<=E2,
      IF(ISNA(MATCH($M$2:$M$5,$AC$2:$AS$2,0)),1))),0))
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •