Returning the first non-empty cell from a column in a pivot table?

troglodyte

New Member
Joined
Sep 9, 2014
Messages
11
Hi all, long time lurker here with a rather frustrating issue!

I have a pivot table which has information regarding a percentage share of sales for products at varying prices over several quarterly cycles (for instance, shirts selling at £5, £10, £15 on Jan 1st, Apr 1st, Jul 1st, Oct 1st etc as a percentage of our entire portfolio of sales). I will include a sample to make this easier to understand.

I am trying to graph the portfolio share of our products at a given date. Over time, the basic price of our goods change so I want to show how this change in price affects the percentage of sales.

Here's an example of what I am working with (apologies if this formats poorly):

Itemprices01/01/201201/04/201201/07/201201/10/201201/01/201301/04/201301/07/201301/10/201301/01/201401/04/201401/07/2014
Socks£5.000.8%0.0%0.1%
Socks£10.00
Socks£15.000.3%0.4%0.2%0.1%
Shirt£80.000.3%0.2%0.9%
Shirt£100.000.7%0.1%0.1%0.1%0.4%
Shirt£120.000.2%0.2%0.9%0.4%
Shirt£150.000.0%0.1%
Trousers£150.000.1%0.1%0.1%0.9%0.6%0.2%0.1%
Trousers£175.000.1%0.2%0.2%0.3%0.3%0.2%
Trousers£200.000.1%0.2%0.2%0.2%0.1%0.1%0.4%0.2%0.1%0.1%0.6%
Trousers£225.000.3%0.1%0.1%0.2%0.3%0.4%0.1%
Trousers£250.000.2%0.2%0.2%0.3%0.6%

<tbody>
</tbody>

What I want to know is which formulae would I use to return me the:

  1. Share of the portfolio at a given date for a specific product (at its basic price), i.e. I want to know what the share of the portfolio was for trousers in January 2012 at its basic price (we can see here that it is 0.1%), what formula would I use to return this 0.1% value (and any other basic item portfolio share)?
  2. The basic price of an item in that quarter (if applicable). For instance the basic price of trousers in July 2012 is £175. What formula would I use to return this (and for any other item too)?

So far I have attempted combinations of the following to no avail:

  • VLOOKUPS
  • HLOOKUPS
  • INDEX
  • MATCH
  • IF

I may have come close to the answer already but not written the formula in the correct order. I appreciate any help people can give me here, thank you for reading.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,187
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top