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!

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.

Dim rng as Range, i as integer
Dim cell as Variant

Set rng = Sheet1.Range("A1:C4")
i=1
For each cell in rng
    If IsNumeric(Cell.Text) Then
         Sheet2.Range("A"& i) = Cell.Text
         i = i + 1
    End if
Next

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!

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

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!

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!

Originally Posted by newdoverman

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.

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

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...

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

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....

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

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

Originally Posted by RobertMika

Never give up..

Attachment 257639

i dont!

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!

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...