Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-then, vlookup, and table display
I have no idea if what I'm trying to do is even possible in Excel. If anyone
could shed some light, it would be greatly appreciated!! I am trying to create a "compounding" document using one tab of the spreadsheet (Sheet 1) as the source. The original spreadsheet has Program descriptions in column B, account amount information in column H and the account number in column I. In a new tab, I would like to group certain account numbers into categories. For example, in row 1, I would have a column heading of Instruction. I know what accounts should fall under this category, so I would like to look up the account numbers in Sheet 1, Column I, and when it finds the defined account numbers, display Program description from Sheet 1, column B and the account amount from Sheet 1, column H, and the account number from Sheet 1, Column I. For each account that is found, I would like the data to display in a new row. Then I would start over, with another category, but in the same spreadsheet. Is this possible with Excel? Might this be easier done in Access? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If-then, vlookup, and table display
On 19 Mar, 20:01, Ash wrote:
I have no idea if what I'm trying to do is even possible in Excel. If anyone could shed some light, it would be greatly appreciated!! I am trying to create a "compounding" document using one tab of the spreadsheet (Sheet 1) as the source. The original spreadsheet has Program descriptions in column B, account amount information in column H and the account number in column I. In a new tab, I would like to group certain account numbers into categories. For example, in row 1, I would have a column heading of Instruction. I know what accounts should fall under this category, so I would like to look up the account numbers in Sheet 1, Column I, and when it finds the defined account numbers, display Program description from Sheet 1, column B and the account amount from Sheet 1, column H, and the account number from Sheet 1, Column I. For each account that is found, I would like the data to display in a new row. Then I would start over, with another category, but in the same spreadsheet. Is this possible with Excel? Might this be easier done in Access? Sure this is possible, but there are a few stages. 1. Tell Excel what the category relationship is, and what the correct category for every row is. Insert a new worksheet in the workbook "CategoryList" Column A - list every single account Column B - list the correct category for that account Insert a new column in Sheet1 "Categories". insert the following formula (I assume your data starts in 2nd row) =VLOOKUP(I2,CategoryList!$A$1:$B$100,2,0) It should put the correct category in the account on that row. Then copy the formula down to the last row. 2. Build a pivot table Highlight the whole table (all rows, and ensure you have the four important columns). I'm assuming you have sensible names for the columns in the first row - if not, put them in. From the menu...Data...PivotTable.... Then click finish. Drag Category into where it says "Drop Page Fields Here" Drag Amount into where it says "Drop Data Items Here" Drag the other two columns into where it says "Drop Row Fields Here" Click on the little down arrow next to where the Category says (All), and choose the Category you want to see. Play about with the Pivot Table - there are many more options - I suspect you might want to move the "Programme" field into the columns. HTH Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP to Display adjacent cell | Excel Discussion (Misc queries) | |||
vlookup display | Excel Worksheet Functions | |||
vLookup - display multiple rows | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
I need a VLOOKUP to display 0 or blank instead of N/A | Excel Discussion (Misc queries) |