Excel wizards handy?

The place for all kinds of software for all kinds of operating systems.

Moderator: Dposcorp

Excel wizards handy?

Postposted on Thu Jun 13, 2013 11:19 am

I'm OK with Excel but I'm having a problem with a spreadsheet. I was given a list of items and attributes, but the list is kind of stupid.

Code: Select all
Item     attribute#     value
A100              1     Blue
A100              2     Green
A100              3     Yellow
A100              4     Orange
A101              1     Purple
A101              2     Cyan
A101              3     Mauve
A101              4     Indigo
A101              5     Brown
...

I can't guarantee how many attributes any of the items have other than it's 5 or less. There are no filler/padding attribute lines (no blank or null values).

Code: Select all
Item     1         2         3         4         5
A100     Blue      Green     Yellow    Orange
A101     Purple    Cyan      Mauve     Indigo    Brown

I'm using Excel 2010. Any hints? Would greatly appreciate just pointing me in the right direction. No this is not my homework. :p
I do not understand what I do. For what I want to do, I do not do. But what I hate, I do.
derFunkenstein
Gerbil God
Gold subscriber
 
 
Posts: 21555
Joined: Fri Feb 21, 2003 9:13 pm
Location: WHAT?

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 11:39 am

what exactly are you trying to do?
Diplomacy42
Gerbil
 
Posts: 63
Joined: Sat Sep 01, 2012 2:56 pm

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 11:40 am

What are you trying to do with this list?

I could see where a pivot table report would summarize things for you.
i7-4770K, H70, Gryphon Z87, 16 GiB, R9-290, SSD, 2 HD, Blu-ray, SB ZX, TJ08-E, SS-660XP², 3007WFP+2001FP, RK-9000BR, MX518
JustAnEngineer
Gerbil God
Gold subscriber
 
 
Posts: 15602
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 11:48 am

The goal is to create a text file in the format I listed in the OP. It'll be imported into another software package, which requires that format. I am digging into Pivot Tables now, thanks for mentioning it. Sometimes my Google-fu works, once I know what something is called. :)

edit: I'm making progress. My item numbers are going down the first column, and I got column headers for each attribute. Now I just need to figure out a way to just show the values rather than have it do some sort of calculation on those values.

edit2: I was hoping that Max or Min might show the actual values, because I can guarantee that attr#+item is unique. And I can verify that using the Count function. But no joy - mix and max both show 0 rather than the value. Seems that MS doesn't think showing the real values is worthwhile, based on what I'm finding online.
I do not understand what I do. For what I want to do, I do not do. But what I hate, I do.
derFunkenstein
Gerbil God
Gold subscriber
 
 
Posts: 21555
Joined: Fri Feb 21, 2003 9:13 pm
Location: WHAT?

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 12:58 pm

This is an ugly hack, but at least it seems to yield the desired results.

First, "translate" the values into numbers by creating two columns holding the possible value names and corresponding numeric values, and use =vlookup to create a new area where the numeric values are shown instead of the value names.

Second, create a pivot table for this new area.

Third, "translate" the values back by creating an inverse lookup table and using =vlookup again.

Not pretty, but it seems to work.
Stargazer
Gerbil
Silver subscriber
 
 
Posts: 49
Joined: Thu Dec 10, 2009 2:28 pm

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 1:03 pm

That is awesome. Thanks for the suggestion, I'll fool around with that. I'm familiar with lookups so it should be pretty easy to do that much.
I do not understand what I do. For what I want to do, I do not do. But what I hate, I do.
derFunkenstein
Gerbil God
Gold subscriber
 
 
Posts: 21555
Joined: Fri Feb 21, 2003 9:13 pm
Location: WHAT?

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 1:08 pm

One thing that I noticed when trying this:
When you do the reverse lookup, if you try to select a cell in the pivot table by clicking on it, it'll give you a GETPIVOTDATA(...) reference in your formula. I just ignored this, and instead manually entered the cell name. Seems to work just fine that way.
Stargazer
Gerbil
Silver subscriber
 
 
Posts: 49
Joined: Thu Dec 10, 2009 2:28 pm

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 1:55 pm

Stargazer wrote:This is an ugly hack, but at least it seems to yield the desired results.


Ugly hack is the canonical way to use Excel.
peartart
Gerbil
 
Posts: 41
Joined: Wed Mar 21, 2012 3:01 pm

Re: Excel wizards handy?

Postposted on Thu Jun 13, 2013 11:38 pm

Stargazer wrote:One thing that I noticed when trying this:
When you do the reverse lookup, if you try to select a cell in the pivot table by clicking on it, it'll give you a GETPIVOTDATA(...) reference in your formula. I just ignored this, and instead manually entered the cell name. Seems to work just fine that way.


The only reason why you wouldn't want to do this is because if you refresh the data, your pivot table can change..
druidcent
Minister of Gerbil Affairs
 
Posts: 2086
Joined: Wed Aug 07, 2002 7:55 pm
Location: Earth, Sol, Milky Way

Re: Excel wizards handy?

Postposted on Fri Jun 14, 2013 9:19 am

Stargazer wrote:This is an ugly hack, but at least it seems to yield the desired results.

First, "translate" the values into numbers by creating two columns holding the possible value names and corresponding numeric values, and use =vlookup to create a new area where the numeric values are shown instead of the value names.

Second, create a pivot table for this new area.

Third, "translate" the values back by creating an inverse lookup table and using =vlookup again.

Not pretty, but it seems to work.

This worked awesome. I just filled a column which serially numbered each line and then the pivot table was sum of that number. Since there's one attribute in each attribute number, the "sum" was just the serial ID. I then pasted (special) the values into another worksheet and used a vlookup the text that I actually want and made a second grid out of it. Then on a fourth (counting the original worksheet plus the new worksheet where the pivot table went) worksheet I built the text file I actually wanted. Thank you to everyone who posted, especially Stargazer who got me what I needed. :) :)

edit: Even then I made it needlessly complex. A plain old lookup to dig through my serial numbers, find the combination, and then populate my grid that way. 6400 records done in about 5 minutes. Huzzah.
I do not understand what I do. For what I want to do, I do not do. But what I hate, I do.
derFunkenstein
Gerbil God
Gold subscriber
 
 
Posts: 21555
Joined: Fri Feb 21, 2003 9:13 pm
Location: WHAT?

Re: Excel wizards handy?

Postposted on Fri Jun 14, 2013 11:14 am

Yay!

Glad it worked. :)
Stargazer
Gerbil
Silver subscriber
 
 
Posts: 49
Joined: Thu Dec 10, 2009 2:28 pm


Return to General Software

Who is online

Users browsing this forum: No registered users and 3 guests