Excel Question #46

[Alex]

For the first one.

Look up pivot tables.

As a simple example:

1. Make sure your state column has a column header (Such as "State")
2. Select Column H
3. Data > Pivot Table and Pivot Chart Report
4. Leave "Microsoft Office Excel list or database" and ""PivotTable" selected.
5. Click Next
6. PivotTable and Pivot Chart Wizard - Step 2 of 3 should show your Column H in the Range field ("$H:$H").
7. Click Next
8. Provide a location where the data can go. Probably you should leave "New worksheet" selected.
9. Click "Layout"
10. Drag "State" into the Row section of the window that pops up. Then drag it into the Data section.
11. Click Ok.
12. Click Finish.

On a new worksheet you should have a list of all the states in the column as well as the count in a separate row. If you want the data free from the pivot table, just copy the cells in the list and paste them to a new location.

[Alex]

For the second one:

Assuming all you need is the original records but limited by date.

1. Where you would have put the =LEFT(G2,4) -- presumabely H2 -- put this formula instead:

=IF(MID(G2,5,2)="04", "AAAAA", "ZZZZZ")

2. This formula says if the 5th and 6th character of G2 equals "04" populate the cell with "AAAAA" otherwise populate the cell with "ZZZZZ". Obviously the fill can be anything but AAAAA and ZZZZZ are likely unique making searching feasible.

3. Drag the formula down the entire column.

4. Sort the spreadsheet in ascending order by column H.

5. Search "ZZZZZ" to find the first non April row.

6. Select all the rows below that and delete them.

[Kevy Baby]

Thanks Alex, I will check that out: I need to learn pivot tables.

I have never taken any classes on Excel (and it shows) - almost everything I know has been due to a need to know how (I am currently learning how to use lookup tables).

[Alex]

Same here. I suck at pivot tables and generally brute force my way through things when the need would otherwise arise. But a count in a single column is pretty straightforward.

[Alex]

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?

[Kevy Baby]

Originally Posted by Alex

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?

I usually only ask if I think that it is something I MIGHT need to use again in the future. If I know something is one-off, I usually just kluge through it. For example, while I didn't come here for learning about Lookup tables and the project I used it on really didn't need it, I still used the opportunity to learn how to use them as I have needed them in the past and I am sure I will in the future.

But for the most part, most of what I do is similar in nature, but not the same thing. If there is something that needs to be done repeatedly, I use programmers at my vendors for that. Also, I need to be careful about using anything more sophisticated than Excel as I often have to share the information with others who struggle just with Excel. In other words, I have to keep what I do user friendly.

I also ask here because I just like to learn something new.

[Ghoulish Delight]

I should write a routine that increments the thread title every time Kevy posts a question.

[Moonliner]

Originally Posted by Ghoulish Delight

I should write a routine that increments the thread title every time Kevy posts a question.

Better declare the counter as long int just in case.....

[Kevy Baby]

I learned something new about Excel today: the maximum number of digits in a number that you can have accurately in a cell is 15. Yes, there is a reason for it (an IEEE standard) and there are ways around it (format the cell as Text or turn on "Precision as Displayed").

A coworker was entering USPS Tracking numbers that were 19 digits and the last four digits were turned into zeroes. Did a little research and this is what I learnt. For our purposes, formatting the cells as Text worked just fine. From browsing the link below, I am not sure of the overall impact of turning on "Precision as Displayed."

http://support.microsoft.com/kb/78113

[Kevy Baby]

Alright, next question on lookups...

I have fallen in love with VLookups and I use them frequently(although for some reason I have yet to need to use an HLookup function). I am such a geek.

I am sure this is another simple one, but my searches haven't provided the results I need. Basically, I need to find the value in an array when value A matches the header across the top and value B matches the list down the side.

For example, if I had three column headers; apple, banana, and orange and three row headers; Ralph, Fred, and George. For simplicity's sake, lets say the values (left to right by row) are 1-9 (apple/Ralph is 1, banana/Ralph is 2, orange/Fred is 8, etc.)

How do I look up the value based on TWO values - so that the look up knows that when value A is (for example) orange and value B is Fred, that the result is "6"?



ETA: for what I needed tonight, I was able to cheat by using a VLookup since the column header info was just the numbers 1-8 (UPS Zones). By using this formula:

=VLOOKUP(J3,'[Lookup table 121010.xlsx]Sheet4'!$A$35:$I$42,H3+1,FALSE)

In this case, you will see I used the value in H3 (Zone) and added one to it. Since the first column of the array was the value that VLookup was looking for a match on, the second column would be Zone 1, the third Zone 2 and so on. By adding 1 to the value of the zone, it made the formula look in the correct column. I just couldn't figure out how to make it look up the correct column based on a text or non-sequential number value.