SOLVED

to get only the marked cells in a row.

Copper Contributor

in one row i have multiple products name (40 products). But i out of 40 i would have marked only few of them.

I need only that marked products to display in another sheet.

can someone help me here.

8 Replies

@coralcg 

How did you mark those products?

by entering the number of quantity that product is purchased. Like 1, 2 etc.

@coralcg 

Let's say that you have a sheet named Products with product names in row 1 and quantities in row 2:

HansVogelaar_0-1718290901276.png

On anther sheet, enter the following formula in A1:

=FILTER(Products!A1:F1, Products!A2:F2>0)

Result:

HansVogelaar_1-1718290988688.png

The formula result will spill to as many cells as needed.

You'll have to adjust this for your setup, of course.

Thank you for the solution.
Can i get the same in column, one below the other.
I want to generate a bill for each row in the data base. Hope you can understand?
best response confirmed by coralcg (Copper Contributor)
Solution

@coralcg 

You can use TRANSPOSE:

=TRANSPOSE(FILTER(Products!A1:F1, Products!A2:F2>0))

@HansVogelaar Thank you very much. I got my solutions.

@HansVogelaar 

coralcg_0-1719679859461.png

Basically i need to generate a bill for each serial number entry. so if i enter the serial number in another sheet, as mentioned before only the marked products should display one below the other for that serial number alone.

@coralcg 

Let's say that

1) The sheet in your screenshot is named Products, and

2) the priduxts are in H1:U1, and

3) You enter a serial number in A1 on another sheet.

Formula for the marked items for this serial number:

=TRANSPOSE(FILTER(Products!H1:U1, FILTER(Products!H2:U100, Products!A2:A100=A1)>0))
1 best response

Accepted Solutions
best response confirmed by coralcg (Copper Contributor)
Solution

@coralcg 

You can use TRANSPOSE:

=TRANSPOSE(FILTER(Products!A1:F1, Products!A2:F2>0))

View solution in original post