consolidate and count if quantity is not zero

[Vagus]

Hello and Thank You in advance!
Column B is what I want to consolidate and count. I only want it to appear on the consolidated list If correlated column C has a value greater than zero.

Bid Code <tbody> </tbody> Breakdown Code <tbody> </tbody> Quantity <tbody> </tbody> 1LPostGLLM10ft3x6 <tbody> </tbody> GLLM 3-1/8 x 6 @ 10ft <tbody> </tbody> 0 1LPostGLLM10ft5x8 <tbody> </tbody> GLLM 5-1/8 x 7-1/2 @ 10ft <tbody> </tbody> 3 2LPostGLLM10ft3x6 <tbody> </tbody> GLLM 3-1/8 x 6 @ 10ft <tbody> </tbody> 1 2LPostGLLM10ft5x8 <tbody> </tbody> GLLM 5-1/8 x 7-1/2 @ 10f <tbody> </tbody> 0 3LPostGLLM10ft3x6 GLLM 3-1/8 x 6 @ 10ft 0 3LPostGLLM10ft5x8 <tbody> </tbody> GLLM 5-1/8 x 7-1/2 @ 10f <tbody> </tbody> 0 4LPostGLLM10ft3x6 GLLM 3-1/8 x 6 @ 10ft 4 4LPostGLLM10ft5x8 <tbody> </tbody> GLLM 5-1/8 x 7-1/2 @ 10f <tbody> </tbody> 7 <tbody> </tbody>

<tbody>
</tbody>

Thanks again for any help.

 

[Ken Cowen]

What do you mean you don't want it to appear? You can filter out the zeroes in the quantity column. You can make them not appear using conditional formatting, or you can write a macro to delete the rows- all three ways will make them not appear, but all three are very different results.
I hope this helps.
Ken

 

[yaroslav89]

I have to agree with Ken. It depends on how comfortable you are with excel and if you want to permanently delete rows with zeroes. Sorting data in column C by unchecking zero column will give you the result you are after by hiding zeroes, but will be easy to sort back to its original state if needed later. Macro will permanently delete those rows.

 

[sandy666]

is that what you want to achieve?

Breakdown Code	CNT
GLLM 5-1/8 x 7-1/2 @ 10ft	1​
GLLM 3-1/8 x 6 @ 10ft	2​
GLLM 5-1/8 x 7-1/2 @ 10f	1​

 

[Vagus]

Thank you Ken, Yaro and Sandy for helping me refine my inquiry.
I need to generate a secondary list that is a consolidated summary of all column B items that have a value greater than zero.
The column B is a repeating list (repeats based on intervals that can be seen column A eg.1L,2L,3L,4L).
My example does not show another condition of my data; that some column B items will have a zero quantity value on all Column A intervals/instances; so those are ones that would not show up at all on the final list.

 

[sandy666]

I think you should show representative example of source data and expected result

 

[Vagus]

I think you should show representative example of source data and expected result

I would love to - I was trying to just past the excel sheet here but that wasnt working and I was trying to paste a screenshot but that also was not working...what is the best way to share the data?

 

[sandy666]

IMHO put example desensitized excel file into any server like google drive or one drive , allow access to it then share link here

one more question: are you allowed to use PowerQuery?

 

[Vagus]

https://drive.google.com/drive/folders/1thSWhkyHhvDHl6s5onVcx5ihaP8vYphV?usp=sharing

The sheet titled QW-GLULAM is the source data. (It has a live feed from another software we are using that populates the quantities.)
The sheet titled TAKEOFF contains the example of what I want the results to look like.

Thanks again for any help!

 

[sandy666]

sorry mate but in preview Quantity is all 0
or (If downloaded) there is a unknown fuction: =ETkoBidQty("1LPostGLLM10ft3x6")