Transpose pivot table/consolidate data?

Hi,

Hope someone can help with little issue I have around consolidating data. I have a list in Excel (2010) which essentially has a lot of duplicated data in it. I have a list of printers and the associated cartridges, but a lot of printers use the same cartridges so that data is duplicated throughout the sheet.

I'm trying to get unique list of the cartridges (as this is the duplicated data) in one column, and then transposed in across the columns is the list of the printers which use that particular cartridge.

When I pull the data into a pivot table it shows the data in the correct way (i.e de-duplicated) but it has all the data in rows, I'm trying transpose across the columns.. I've attached an example sheet to try and demonstrate what I mean, hopefully the above makes sense I'm finding it difficult to explain without a lot more words!

I can definately expand though if anyone could help/need more explanation, thinking about it, essentially the result would be something along the lines of putting the data into tables in a DB to make them more efficient/reducde dupliation.

Any help is greatly appreciated!

Thanks.

Hi,

Thanks, not sure if its because don't fully understand the formula but it doesn't seem to get the results I'm trying.. As far as I can see it just pulls the first result rather than all of the corresponsding results into the cells? I'm maybe just missing it though as don't fully understand the formula.

I've attached an example with some more data and the actual pivot/result, would you mind taking a quick look at this one?

really appreciate the help!

Sorry I didn't see your attached file! I'm just taking a look over it now!

Hi, thanks so much for the replies.. Both worked I think it looks like, the second example (thanks canapone) from the ciaociao sheet worked but I just couldn't extend the range for some reason, I changed the formula to include extra cells but it returned totally blank cell (no errors).

From your sheet excelxexcel looks like it works great too thanks and I managed to extend the ranges too when I unhid the columns.

Really appreciate all the help, still don't fully understand the formulas but I'm getting there with it.

Thanks again.

I am creating a movie schedule...

here is the sample data...

TITLE DATE TIME
CHOCOLAT (2000) 2-Apr-14 10:00 PM
ERASED 3-Apr-14 12:30 AM
ET THE EXTRA TERRESTRIAL (RESTORED) 3-Apr-14 2:30 AM
FAST AND FURIOUS 6 3-Apr-14 5:00 AM
ERASED 3-Apr-14 7:30 AM
ET THE EXTRA TERRESTRIAL (RESTORED) 3-Apr-14 9:30 AM
FAST AND FURIOUS 6 3-Apr-14 12:00 PM
ERASED 3-Apr-14 2:30 PM
ET THE EXTRA TERRESTRIAL (RESTORED) 3-Apr-14 4:30 PM
FAST AND FURIOUS 6 3-Apr-14 7:00 PM
ERASED 3-Apr-14 9:30 PM
ET THE EXTRA TERRESTRIAL (RESTORED) 3-Apr-14 11:30 PM
FIELD OF DREAMS 4-Apr-14 2:00 AM

and i want it to be similar to this one
4/3/2014
ERASED 12:30 AM 7:30 AM 2:30 PM 9:30 PM
ET THE EXTRA TERRESTRIAL (RESTORED) 2:30 AM 9:30 AM 4:30 PM 11:30 PM
FAST AND FURIOUS 6 5:00 AM 12:00 PM 7:00 PM


is they a way to simplify this?