If-then, vlookup, and table display

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
Click to expand...


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....
Click to expand...
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
 
Back
Top