• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup and Return Entire Row Starting with Non-Zero Cell

acehit

New Member
Hi All,

Trying to do something that's a bit tricky. I currently have two worksheets and am trying to pull in an entire row from one of them. The row pulled in would be based on a criteria that is shared on both worksheets (unique identifier). These are located in the left most column on their respective worksheets. Here's the tricky part. I'd like the row to start with the first non-zero value. I've tried several array formulas but all have not worked. Here's the set up of the raw data, the target worksheet, and the desired end result. A sample workbook is also attached.

Source Data Worksheet
2v9w21g.jpg


Target Worksheet
30m1qft.jpg


Desired Output
dcwp4l.jpg
 

Attachments

  • Sample Data.xlsx
    10.4 KB · Views: 7
AceHit

Firstly, Welcome to the Chandoo.org Forums

In Target Worksheet (Before)! B2:
=IFERROR(INDEX('Source Data'!$B$2:$F$5,MATCH($A2,'Source Data'!$A$2:$A$5,0),SMALL(IF('Source Data'!$B2:$F2>0,COLUMN('Source Data'!$B2:$F2)),COLUMNS($B2:B2))-1),"") Ctrl+Shift+Enter

Copy across and down

Or see attached file:
 

Attachments

  • Sample Data.xlsx
    11 KB · Views: 11
Last edited:
=IFERROR(INDEX('Source Data'!$B$2:$F$5,MATCH($A2,'Source Data'!$A$2:$A$5,0),SMALL(IF('Source Data'!$B2:$F2>0,COLUMN('Source Data'!$B2:$F2)),COLUMNS($B2:B2))-1),"")

Hi Hui.

Just a small point, but will the OP necessarily know what amendments they need to make to the constant used in this expression (-1) should they for whatever reason decide to change the range for their source data?

For example, imagine if the OP decided to insert two extra columns to the left of their data table in the Source Data tab, so that, instead of being in A1:F5 it was in C1:H5.

The column insertions will force the ranges being referenced in your formula to change appropriately, so that part is ok. However, the results in e.g. row 2 will now all be blank, the reason being that the construction you are using is here dependent on the value of the static constant at the end (-1).

Of course, the OP might work out that this needs to be changed; and they might even work out to what it needs to be changed (-3 in this case).

However, none of this re-thinking and re-calculating as to what this value should be is necessary if we employ a more rigorous construction, i.e. (in B2):

=IFERROR(INDEX('Source Data'!$B$2:$F$5,MATCH($A2,'Source Data'!$A$2:$A$5,0),SMALL(IF('Source Data'!$B2:$F2>0,COLUMN('Source Data'!$B2:$F2)-MIN(COLUMN('Source Data'!$B2:$F2))+1),COLUMNS($B2:B2))),"")

in which this part:

COLUMN('Source Data'!$B2:$F2)-MIN(COLUMN('Source Data'!$B2:$F2))+1

will always give the required array for this construction, irrespective of the range in which the source data lies, and there is thus no need for the addition/subtraction of a fixed constant, a constant which would need to be re-determined each time the source range changed.

Regards
 
a more succinct way would be
B2:
=IFERROR(INDEX('Source Data'!$A$2:$F$5,MATCH($A2,'Source Data'!$A$2:$A$5,0),SMALL(IF('Source Data'!$A2:$F2>0,COLUMN('Source Data'!$A2:$F2)),COLUMNS($A2:B2))),"")
 
a more succinct way would be
B2:
=IFERROR(INDEX('Source Data'!$A$2:$F$5,MATCH($A2,'Source Data'!$A$2:$A$5,0),SMALL(IF('Source Data'!$A2:$F2>0,COLUMN('Source Data'!$A2:$F2)),COLUMNS($A2:B2))),"")

Not sure I understand. That suffers from precisely the same potential issues as I outlined above.

Regards
 
Your correct
My mod only fixed inserted Columns after column A not before Column A
 
@Hui this formula works, but there's one corner case. In rows with the following sequence the leading zeros are removed, but those between numbers are also removed (in the case below its the zero between 100 and 200)

Example
0,0,100,100,0,200

Is there a way to modify the formula so that only leading zeros are removed but others between numbers are kept (see below):

Desired Result
100, 100, 0, 200
 
May be something like this. I have not tested extensively let me know if it serves the purpose its an array formula.

Code:
=OFFSET(D20,,MATCH(TRUE,INDEX($D20:$I20<>0,0),0)-1,,MATCH(TRUE,ISNUMBER(1/$D20:$I20),0)-MATCH(TRUE,INDEX($D20:$I20<>0,0),0)-1)
Thanks
 
@Nebu unfortunately the formula returns #N/A errors (despite ctrl - shift - enter)

@Hui are there any modifications to your formula that would yield the desired results above?
 
Hi:

Find the attached , THE FORMULA WAS GIVING #N/A BECAUSE OF THE CELL REFERENCE WAS D20.
 

Attachments

  • Sample Data.xlsx
    10.8 KB · Views: 3
Target Worksheet B2:
=OFFSET('Source Data'!$A$1,MATCH($A2,'Source Data'!$A$2:$A$5,0),MIN(IF(OFFSET('Source Data'!B$1:F$1,MATCH($A2,'Source Data'!$A$2:$A$5,0),0)>0,COLUMN(B1:F1),FALSE))-1,1,COUNTA(B1:F1)) Ctrl+Shift+Enter
Copy across
Copy B2:F2 Down

see attached:
 

Attachments

  • Sample Data.xlsx
    10.8 KB · Views: 5
@Hui I'm close to getting this to work but encountered an error. Part of the problem is that the source and target data sheets are slightly different than the setup I provided. There is one additional column between the lookup column and array of values. I've attached a more representative worksheet that illustrates this set up. As you'll see, the first row in the "Target" worksheet operates well but subsequent rows do not. They either show incorrect values or #ref errors.

Could you look at the attached and help troubleshoot?
 

Attachments

  • sample data revised.xlsx
    11 KB · Views: 3
Back
Top