How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Have a data like this: Col1 Col2 Col3 C1 R1 V1 C1 R2 V2 C1

Customer Question

Have a data like this:
Col1 Col2 Col3
C1 R1 V1
C1 R2 V2
C1 R3 V3
C2 R1 V1
C3 R2 V2
C3 R3 V3
C4 R3 V3
C5 R2 V2
C6 R2 V2
Need to pivot this to something like this (with value cells filled in with format of col3)
Rows C1 C2 C3 C4 C5 C6
R2 X X X X
R3 X X X
R1 X X
I may need to share a screenshot or two to really show this. Probably less than 15min for someone who works with Pivot tables often. Willing to pay $10.
Submitted: 9 months ago.
Category: Microsoft Office
Customer: replied 9 months ago.
Attached are: data.png and pivot.pngdata.png: Data in the excel
pivot.png: Expected pivot table with appropriate color formatColumn2 and Column3 always go together, meaning a value of R1 is always going to have V1The "X" in pivot is optional. The color-fill-in is good enough.
Expert:  Bruce Wilner replied 9 months ago.

This is, to say the least, anything but self-explanatory. One has no idea what you want.

Customer: replied 9 months ago.
HelloIt is a simple problem to solve in my mind :-). Have a data file and needs to convert that into some kind of Pivot table view - with color coded cells to see it all at one place. Let me know what questions you have.
Expert:  Bruce Wilner replied 9 months ago.

If it's a simple problem to solve in your mind, that's because you know what the problem is--but you haven't succeeded in describing it to anyone else, other than by saying, "Wow, is this simple!"

"Color coded" cells are lovely. What exactly do the colors mean?

"Some kind of pivot view table" is unacceptably vague. One does not program computers by specifying unacceptably vague tasks, but, rather, exhaustive detail, and then some. I'll settle for just, "This is what I am trying to accomplish: yadda yadda yadda," never mind the exhaustive detail.

Thank you for your anticipated cooperation.

Customer: replied 9 months ago.
Ok, got your point. I am using this system for the first time, so not really sure how to "describe" my question.See attached is the sample excel data file. All I want to do with this data table is to be able to generate "report" (transpose or pivot or whatever) that will show my data as in the pivot.png image.Pivot is almost working with the layout "rows" and "columns" but I would like to see the format (color of the cells).The "col3" is essentially an enumeration that is identified with different colors - think of it as different types of things. Each type is identified with a different color. I have coded this as a simple conditional formatting right now, but I can use something like vlookup for it. I am trying to see how to conditionally format cells within PIVOT table, to show the cells in different colors.Ideally, I don't want to display "count" (that is "1") as value in the cell - instead just "color" the cells.
Expert:  Bruce Wilner replied 9 months ago.

Not sure why the mention of "count" even appears.

ALL YOU REALLY WANT is to display your output color-coded instead of in boring visual form.

Of course, that's rather a complex task for Excel--Excel because it's PROGRAMMATIC and we have to write a detailed macro that actually achieves the work (the equivalent of the user [= you] walking through a detailed dialogue), although it's a trivial one for Access. Any particular reason we must use Excel?

Customer: replied 9 months ago.
The data is simple CSV that we manage. Access isn't something everyone have on their desks, but excel is ubiquitous. So, there is no way to conditionally format cells in a pivot?
Expert:  Bruce Wilner replied 9 months ago.

You can do anything in a macro. A formula won't cut it. Not every version will have the same capabilities exposed through macros. Also, as you move from version to version, umpteen functions disappear. This makes Excel programming extremely dicey--strange for a company that claims that backward compatibility is always one of its principal goals.

Let's look at this in the morning, O.K.? It's getting late here on the Yankee Coast. You may be surprised at the volume of work required to do something as simple as turn something red or yellow. It ALMOST isn't worth it. (You[ll find a Web page on "copy formatting from Excel cell using formula based on condition," but it ain't so: it sets up the formatting beforehand using BUTTONS, and the "formula" does precious little.)

Related Microsoft Office Questions