+ Reply to Thread
Results 1 to 28 of 28

extract number values from a table and make a list

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    extract number values from a table and make a list

    hi

    how can i extract all number values from a table column and list them on a separate worksheet?
    there are blanks in the table column, which have to be omitted.

    thank you for your help!

  2. #2
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: extract number values from a table and make a list

    Consider if the A1 to C4 is table within Excel then there are some numbers are filled within this area and some are not filled.
    And this information is available in Sheet1. Below my code will take the numbers and put in the Sheet2.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    it's actually more complicated: see attached file

    i need to populate my list with the values (late payments) from table 1.
    clients without any dues have to be omitted.

    i have none of the formulas yet, but believe once the list contains the values/late payments, i can lookup the client, country and type.

    thanks for any input!
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: extract number values from a table and make a list

    I had few questions

    * Do you want this in VBA or Functions
    * So you want the list whatever is present in Table 1 into the other sheet in the same way.
    * If the List in other sheet is available do you want this to be appended or check if the customers are present and add to Overdue amount

  5. #5
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    hi shyamhappy

    - i'd prefer functions, cus i dont know VBA.
    - yes, but without the blank rows, i.e. without the clients that have no amounts overdue. pls understand, the actual list is much longer!
    - dont fully understand this last question: basically, the list should extract all clients with overdue amounts. unfortunately, it cant be done with pivots or regular filters, as you cannot apply multiple filters at once.

    appreciate your help!

  6. #6
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    sample_v3.xlsx

    shyamhappy - here's a new approach:
    in the attached sample sheet, the cells in red are my problem: the formula always shows the same client, instead of going down the table and looking for others that have late payments.

    any ideas?

    thank you!

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: extract number values from a table and make a list

    Try this array formula in A3. Confirm with Ctrl+Shift+Enter not just Enter Before dragging Across and Down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 08-12-2013 at 12:17 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Little bit more robust( in case you need to add rows) A3 drag down
    =IFERROR(INDEX($O$3:$O$2000,SMALL(IF($V$3:$V$2000>0,ROW($W$3:$W$2000)-ROW($O$3)+1),ROWS($A$1:A1))),"")
    Confirm Control+Shift+Enter
    or
    =IFERROR(INDEX($O$3:$V$2000,SMALL(IF($V$3:$V$2000>0,ROW($W$3:$W$2000)-ROW($O$3)+1),ROWS($A$1:A1)),MATCH(A$2,$O$2:$V$2,0)),"")
    Drag down and accross
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: extract number values from a table and make a list

    A non array based solution that is easily expanded.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Quote Originally Posted by newdoverman View Post
    A non array based solution that is easily expanded.
    You can replace column W with:
    =IF(V3>0,MAX($W$1:W2)+1,"")
    but column I requires manual entry which as I assume is not very ...convinient.

  11. #11
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    thanks a lot guys! i could make it work with your formulas!

  12. #12
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Quote Originally Posted by mischge View Post
    thanks a lot guys! i could make it work with your formulas!
    You are welcome.

  13. #13
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    one more question:
    on excel 2010, there seem to be issues with vlookup.
    i am trying to use vlookup to match the clients in the list with the the client in the table and return client's country.

    however: =VLOOKUP($A3;Table1;Table1[Country];FALSE) returns #N/A
    (Table1 and obviously Table1[Country] are on a separate worksheet, but in the same workbook.
    the tables are ranges converted to tables.

    does anyone know of any issues?
    Last edited by mischge; 08-13-2013 at 04:51 AM. Reason: added info

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: extract number values from a table and make a list

    Quote Originally Posted by newdoverman View Post
    A non array based solution that is easily expanded.
    If more than one client is over due by the same total this will only return the first client found.

  15. #15
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: extract number values from a table and make a list

    Quote Originally Posted by mischge View Post
    one more question:
    on excel 2010, there seem to be issues with vlookup.
    i am trying to use vlookup to match the clients in the list with the the client in the table and return client's country.

    however: =VLOOKUP($A3;Table1;Table1[Country];FALSE) returns #N/A
    (Table1 and obviously Table1[Country] are on a separate worksheet, but in the same workbook.
    the tables are ranges converted to tables.

    does anyone know of any issues?
    I don't use Tables often and can't see why that should be wrong, but seems it is ...
    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change the commas to semi-colons to suit your system.

  16. #16
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    i tried that, but doesnt work. neither will a number for the column...
    couldnt find anything on the web to explain this either...
    also tried converting the table back to a range, but no success...

  17. #17
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Could post your workbook?

  18. #18
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: extract number values from a table and make a list

    Can you post a sample of your actual workbook,desensitised, so we can see the layout we are dealing with?

  19. #19
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    sorry guys, it works in the sample workbook, but not in my actual one...

  20. #20
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    ok gents! attached file contains simplified sheets from my actual workbook.
    on the 'S.O.S' tab, i need to extract the late paying companies and fill in the data to the right (i.e. late amounts).

    no clue why the vlookup does not work...

    thanks for your help! once again....
    Attached Files Attached Files

  21. #21
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Try B3 down and accross
    =IFERROR(VLOOKUP($A3,Table1,MATCH(B$2,Table1[[#Headers],[Client]:[Total Overdue]],0),FALSE),"")

  22. #22
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    thank you, but unfortunately no luck either... i dont think any of us is doing anything wrong. must be somewhere in the background...

  23. #23
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    Quote Originally Posted by mischge View Post
    thank you, but unfortunately no luck either... i dont think any of us is doing anything wrong. must be somewhere in the background...
    Never give up..

    sample_vlookup(1).xlsx
    Last edited by RobertMika; 08-14-2013 at 03:28 AM.

  24. #24
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: extract number values from a table and make a list

    Here's Roberts' formula in your workbook.

    If there is no match for the headers a blank column will be returned
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    cant get it to work in my workbook...
    but found a work-around, which works:

    =INDEX(Table1;MATCH($A4;Table1[Client];0);6)

    (if i put Table1[Country] instead of "6", it again doesnt work. not sure what the issue is, but suspect it's something with the headers in the table???)

    thanks a lot for your help!

  26. #26
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    Quote Originally Posted by RobertMika View Post
    Never give up..

    Attachment 257639
    i dont!

  27. #27
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: extract number values from a table and make a list

    To keep absolute reference to one column /row when using tables you have to use INDIRECT:
    B2:
    =INDEX(Table1,MATCH($A3,INDIRECT("Table1[Client]"),0),MATCH(B$2,Data!$A$1:$L$1,0))

    or
    =INDEX(Table1,MATCH($A3,INDIRECT("Table1[Client]"),0),MATCH(B$2,INDIRECT("Table1[#Headers]")))
    Drag that accross
    Last edited by RobertMika; 08-14-2013 at 03:52 AM.

  28. #28
    Registered User
    Join Date
    07-30-2013
    Location
    binningen
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: extract number values from a table and make a list

    cool, thanks. good to know.

    one more question:
    how do you show the same client, who has 2 late payments, in 2 rows?
    (essentially, the same client might be served by 2 desks, hence would need the distinction)

    i am currently using an array funtion:
    =IFERROR(INDEX(Data!$D$2:$D$515;SMALL(IF(Data!$P$2:$P$515>0;ROW(Data!$W$2:$W$515)-ROW(Data!$D$2)+1);ROWS(Data!$D$1:D1)));"")

    going to look at other forum posts now...

+ 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. Replies: 0
    Last Post: 05-30-2013, 03:11 AM
  2. Replies: 1
    Last Post: 01-14-2013, 04:01 AM
  3. How to extract rows from a table list
    By simke in forum Excel General
    Replies: 0
    Last Post: 12-18-2011, 10:13 AM
  4. Extract number from list
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-03-2010, 06:41 PM
  5. extract the top 6 values in an increasing number of values
    By s1dlaw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2007, 09:19 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