Item Sequencing

**keby1nko** · 01-08-2015, 10:19 PM

Hi guys i'm now working on a spreadsheet and was wondering whether this is achievable by using excel basic formula:

I have created a databank for a list of item with their accordance price.

For Instance (Sheet1):
Cell A1=Item A = $8.00..........Quantity = 1
Cell A2=Item B = $13.00.........Quantity = 0
Cell A3=Item C = $18.00.........Quantity = 1
Cell A4=Item D = $23.00.........Quantity = 0

Let say Customer A would like to buy Item A and Item C only is there a way to display the final list as:

For example (Sheet 2):
Cell A1=Item A = $8.00..........Quantity = 1
Cell A2=Item C = $18.00.........Quantity = 1
**Item B and Item D will not be shown

I would like excel to automatic capture those item with quantity>1 only and arrange them in order.

Can anyone provide any idea on this?

**sktneer** · 01-08-2015, 11:17 PM

Assuming you have three columns for Item#, Price and Qty on Sheet1, then try this Array Formula on Sheet2. Since this is an Array Formula so you need to confirmt it with Ctrl+Shift+Enter instead of Enter alone.

On Sheet2
In A1

Please Login or Register  to view this content.

and then copy across and down. Format the price column on Sheet2 as currency.

For detail, see the attached.

**keby1nko** · 01-08-2015, 11:45 PM

Thank a lot for your help! is there a way to enter the quantity without the need to press Ctrl+Shift+Enter to make it more user friendly?

**sktneer** · 01-09-2015, 02:08 AM

I think the most user friendly way is to use a macro which runs in the background and user doesn't need to input any formula on the sheets.
Please find the attached workbook where there are two Sheets "Data" and "Summary". Where Data sheet contains the raw data and Summary sheet will contains all the items whose quantities are greater than 0.
So suppose you are on Data sheet and fill the data on Data Sheet and once you select or activate the Summary Sheet, the relevant data will be populated on the Summary Sheet automatically based on your set condition.
Enable the macro after opening the workbook when prompted.

See if this is something you can work with.