0

I have data that contains a lot of 0's and several values > 0 in it. I need to get these non-zero values.

I'm wondering if MATCH can understand something like MATCH("<>0" or match if number because I could just change the 0's to text like "blank" or something.

EDIT: This is only a part of what I'm trying to get, if it will help, I can explain what I'm trying to pull out of my data. Here's a sample of what my data looks like.

Sample Data Example

What I need is to find all non-zero values in my table and return the respective column and row headers and the actual value. Similar to this post I found, I'm currently using a variation of those formulas. As a temporary solution, I put an underscore in front of my non-zero values to use MATCH("_*" .

Formulas I'm Using

=IF(ROWS(B$100:B100)>COUNTIF($B$2:$DMV$91,"<>0"),"",INDEX(A$2:A$91,SMALL(IF($B$2:$DMV$91<>0,ROW($B$2:$DMV$91)-ROW($B$2)+1),ROWS(B$100:B100))))

=IF(B101="","",INDEX(B$1:DMV$1,MATCH("_*",INDEX(B$2:DMV$91,MATCH(B101,A$2:A$91,0),0),0)))

Desired Output

Date | Item | # of Access

11/1/2017 | Item1 | 1

11/2/2017 | Item2 | 1

11/3/2017 | Item3 | 8

11/3/2017 | Item4 | 4

Not sure if this information is helpful but as an extra, if someone could point me in the right direction of how to display the actual value in the cell, that'd be awesome.

raccoon_nine
  • 43
  • 1
  • 8
  • You can simply filter the data to remove all the `0` and then copy and paste the result to another sheet. – Scott Craner Nov 30 '17 at 18:17
  • Now show what your desire output would look like. – Scott Craner Nov 30 '17 at 18:54
  • I would be best if you unpivoted your data first You can use Power Query to do that and it would remain live. Then you can simply filter the new table using a simple filter. To use power query to un-pivot, see the second answer here: https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal – Scott Craner Nov 30 '17 at 19:15
  • Will try, thanks for all the help so far! – raccoon_nine Nov 30 '17 at 19:20

1 Answers1

0

You can use this array formula to remove 0's from a vertical array (e.g. the range A1:A10):

= INDEX(A1:A10,N(IF({1},MODE.MULT(IF(A1:A10<>0,(ROW(A1:A10)-MIN(ROW(A1:A10))+1)*{1,1})))))

Or this array formula to remove 0's from a horizontal array (e.g. the range A1:J1):

= INDEX(A1:J1,N(IF({1},TRANSPOSE(MODE.MULT(IF(A1:J1<>0,(COLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1)*{1;1}))))))

Note these are array formulas, so you must press Ctrl+Shift+Enter after typing this formula rather than just Enter.

This assumes that you will use this result as an intermediate result, because this result returns an array (which can't all display in one cell).

ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51