dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
4570
share rss forum feed

nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

[Excel] Add all fields to pivot table (in Row Labels) (Excel 201

Title says it all, I would like to add all available fields in my pivot table.

I know I could manually click each of them, but I have several hundreds to go through, each and every day.

Any quick way to do this?

I'm including a screenshot..basically want a way to select all fields and drag em' in there.

Thanks


Jtmo
Premium
join:2001-05-20
Novato, CA

1 edit

Re: [Excel] Add all fields to pivot table (in Row Labels) (Excel

Click for full size
I just add all fields in the table (Select all data on the sheet), add to report filter, then on the actual A1 cell you should have the field name (All) with a drop down but at the bottom is select multiple items check box.

It appears you simply created a list of Baking needs vs. categories to place into a table? All those items would be in a column labeled 'Baking Needs' with a separate column of 04x78 and 08x78 in another column or something along those lines?

nickstoy

join:2001-02-03
Saint-Lazare, QC

Im not sure what you mean in your explanation.

The pivot comes from a DB which stores thousands of planograms, for hundreds of catefories (you only see baking in the pic), and which product (UPC) is carried on which planogram.



Jtmo
Premium
join:2001-05-20
Novato, CA
reply to nickstoy

There are multiple 'types of tables' depending on how your data is organized. This might explain the difference and show you why you are running into this »www.mediafire.com/file/dtvyymhyj···_10a.pdf

I am far from a guru, but I do use Pivot's quite often. I hope I have helped until a guru arrives. I now have to go peel 20 pounds of potatoes.


nickstoy

join:2001-02-03
Saint-Lazare, QC

Potato guru? lol...thanks for your help



JohnInSJ
Premium
join:2003-09-22
Aptos, CA
reply to nickstoy

That looks like a messed up data connection to me. What version of excel is this?
--
My place : »www.schettino.us


nickstoy

join:2001-02-03
Saint-Lazare, QC

2010



JohnInSJ
Premium
join:2003-09-22
Aptos, CA

And what's the database that is the source for the pivot table?
--
My place : »www.schettino.us



JohnInSJ
Premium
join:2003-09-22
Aptos, CA
reply to nickstoy

And (last question, I promise) what is the SQL (or whatever DB) query & first 10-20 rows of the result look like?

Basically, the "fields" you are showing above look more like values in a single column - no database in its right mind would have each of those as a field in a single record. So, I am wondering how the source data is organized.
--
My place : »www.schettino.us


nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

Click for full size
That's where it gets complicated. I get the data thru a vendor portal, which downloads to .txt. I then import it to Excel.

Basically, I have two data tables. I don't know exactly how we call them, it's the type where you have populated fields in both axis, but data only where the two joins. I'm inculding a dummy screenshot just for the sake of understanding.

Both tables contain similar fields, and my goal is to end up with one table.

1st table contains a list of stores on the vertical axis, and different planogram names on the horizontal axis. Then there are "X"s to tell us which store has which planogram.

2nd table has Product names on the vertical axis, and planogram names on the horizontal axis. Then there are numbers to tell how many facings we should carry in that planogram, for that product.

Basically, what I need is one big table telling me which store carries what product, and how many facings.

Oh, and just to make this fun, there are over 1000 planograms, over 60,000 products, and 900 stores.


mmainprize

join:2001-12-06
Houghton Lake, MI

1 edit
reply to nickstoy

Never mind it did not work


nickstoy

join:2001-02-03
Saint-Lazare, QC

yeah I just tried your combo


nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable
reply to nickstoy

was once told if you can't do something in Excel in less than 10 minutes, it's the wrong way. So I must be doing this wrong.

Anyone has idea on how to get my final result out of those two tables?

I need: Store #, planogram, product, facings



JohnInSJ
Premium
join:2003-09-22
Aptos, CA
reply to nickstoy

Yep, sorry yesterday got away from me.

You have what we would call in the database world non-normalized data there

For a pivot table to work, your data should look like an relational database table: one row per record, where every row's columnar data is an instance of the same data.

So, for example, your data in relational form is

Product | Planogram
A | 4
B | 1
B | 1
C | 3
D | 2
D | 2
D | 2
D | 3
E | 1
E | 4
E | 4
E | 4

Store | Planogram
1 | 1
2 | 3
3 | 2
4 | 4
5 | 3

Is there any way to get the data in THAT format? I'll see if there's a way to decompose your source data into the proper form in excel.
--
My place : »www.schettino.us


nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

yeah, you summarized very well my situation. The reason I posted in the first place is because I'm trying to get my data to be relational.

The thing is, the result would be more like this:

Product | Planogram 1 | Planogram 2 | Planogram...
A | 1 | 1 | 2
B | - | - | -
C | - | - | 2

Store# | Planogram
1 | 1-6-9-13-15-19...
2 | 2-12-13-16-19...


nickstoy

join:2001-02-03
Saint-Lazare, QC

Ok, I've decided to give Access a try in order to blend those reports together.

Mods, you can lock this up.

Thanks to everyone who tried