I need help with microsoft excel!?

Question

Ok so i have a table of zip codes. I used a pivot table to condense these to find duplicates and then entered in the county names. Then on my original sheet i tried using an if statement to automatically find and enter these counties according to their zip code, but sense there are multiple entries with the same zip (duplicates) it comes up with an error (false in this case).


What formula should i use to compare sheets?!?!?

Answer 1

Use the pivot table as a lookup field Zip - Country. Sort in increasing order by Zip. Then in the column next to your Zip codes on the original sheet type =vlookup(a2,Pivot!$a$3:$b$100,2) where a2 is the cell reference for the first zipcode,Pivot is the name of the sheet with the pivot table and a3 is the first data cell of the pivot table and b100 is the last cell of the range. And Country in in the second column of the pivot table range. It easier to enter the values by click and dragging. This should put the country with that zip code in the cell on you original sheet.

If the result is what you want, drag the formula down.

Answer 2

Your question is a little unclear as to what you mean by 'automatically find and enter these counties'.



Do you mean create a list somewhere on the worksheet that contains all county names included in a particular zip code?



If so, you can use a macro to do that. The following macro assumes the Zip Codes are in column A and Counties in column B.



If you wish to use different columns for your specific situation, modify the macro:



Change both "A" references to the column letter your zip codes are in, i.e. "C"



Change the "B" reference to the column letter your counties are in, i.e. "D"



Change the "L", "L1", and "L:L" references to the column you wish results to appear, i.e. "M", "M1", and "M:M" respectively.



Then copy this macro, modified if desired, to the clipboard:



Sub CountybyZip()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Columns("L:L").ClearContents

selZip = InputBox("Please enter a zip code to extract.", "Zip Code")

Range("L1").Value = "Counties for Zip Code " & selZip

For i = 1 To LastRow

If Cells(i, "A").Value = Val(selZip) Then

Range("L" & Rows.Count).End(xlUp).Offset(1, 0).Value = _

Cells(i, "B").Value

End If

Next

Columns("L:L").AutoFit

Columns("L:L").HorizontalAlignment = xlCenter

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options..



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



Press CTRL + your shortcut letter to run the macro.



Enter a zip code and press Enter.

Answer 3

Lemme guess, you're in a Computer Literacy class... maybe with the Art Institute??? Because whats funny is that's the EXACT question of this weeks first assignment. weird. "Week 3: Week 3 - W3: Assignment 1 Discussion Discussion Question 3 Compare Microsoft Word, Microsoft Excel, and Microsoft PowerPoint. When should you use each of these applications? When can you use these applications interchangeably? Give examples and reasons to support your answer."