take values from column A for values>0 in column B

M

Mauricio G.

I have a table similar to Sheet 1 and need convert to Sheet 2, Exist a
function or how work???

Sheet 1 Sheet 2
Cod Value | C1 C5 C7
C1 25 |
C2 |
C3 | ===>
C4 |
C5 10 |
C6 |
C7 2 |
 
M

Max

Hazarding a guess here, piecing from your subject line and posting ..

Presume you want to extract horizontally & bunched to the left in Sheet2,
only the codes: C1, C5, C7 (in Sheet1's col A) for values > 0 in the "Value"
col (in Sheet1's col B)

Data is assumed running in row2 down in Sheet1's col A and B

In Sheet2,
Put in A2: =IF(Sheet1!B2>0,ROW(),"")
Copy down to cover the max expected extent of data in Sheet1's col B. Leave
A1 empty. Hide away col A.

Put in B2:
=IF(COLUMNS($A:A)>COUNT($A:$A),"",INDEX(Sheet1!$A:$A,SMALL($A:$A,COLUMNS($A:A))))
Copy B2 across to cover the max expected number of returns. In B2 across
will appear the required results: C1, C5, C7 (based on your sample posted)
with all results neatly bunched to the left.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top