Summerize Data from one tab to another

[tdorsi]

I have a very large spreadsheet provided to me on a monthly basis that contains the Fee information for a group of banks.

The Data is broken down by Bank Name and then Location code in Column (A). The Bank Names are consistent from month to month however the location codes are variable, if a new location is added or one closed. The location codes are variable and do not match the bank name


The Data tab is laid out like this
Column A Column B Column C Column D
Blank Fee 1 Fee 2 Fee 3
Bank A Blank Blank Blank
Loc 1 $ $ $
Loc 2 $ $ $
Bank B Blank Blank Blank
Loc 1 $ $ $
Loc 2 $ $ $

I would like to summerize the data from this sheet to a new tab by bank name. I have created an example of the data layout but I am not able to upload the image. I can email an example spreadsheet.

 

[Trevor G]

Welcome to the forum, you can't add pictures to this forum, so you either need to copy and paste the data sample or use something like the forum HTML converter or Excel Jeannie to display samples in your thread.

 

[tdorsi]

Thanks Trevor,

Can you see this link?
http://postimage.org/image/2ftxxwolg/

 

[Trevor G]

Yes can see the image, have you tried looking at Pivot Tables, you can group in them and also you can then get a single sheet based on a field name in this case your Bank Names.

Which version of Excel are you using as 2007 and 2010 create Pivot Tables in different ways compared to 2003 or earlier!

 

[tdorsi]

I had not thought about a pivot table. I am using 2007. I was trying to use a formula or vba to pull the data to a different tab because I need to bump the summary up against a total from another report.

Here is an example of the balancing tab layout.
http://postimage.org/image/2h9e7tdok/

 

[Trevor G]

Try the Pivot table idea to see if it works if not then let us know.

Pivot table is found under the Insert Tab on Right, once designed you can then place the Bank Field in the Report position, then in the Options Tab on the right you will find a drop down and show report pages is there that will then generate a tab per Bank name.

 

[tdorsi]

Im not sure if the pivot table is going to work. I just tried on my spreadsheet and Each location code shows as a filter option as well as the bank name. Under each bank name there could be as many as a 100 locations. I can filter each location but the Bank Name returns nothing because that row is empty. I need to total all the locations under each bank name. The real worksheet is very large and is not in proper table format as you can see in the 1st image example.

Thank you in advance for any thoughts or solutions.

 

[Trevor G]

Do you have a list of each bank and each location the bank is located in?

My only other thought at this time is to insert a column and place in formula to link to the bank name and then file down and then repeat this, I understand this is not the best solution yet it will achieve your goal.

Other than that you could ask who ever gives you the file to include the Bank names as well as the location prior to you receiving it.

 

[Roli001]

Your problem is that you have the "BANK xyz" names in rows above actual data and not in every row corresponding to that bank data so we need to populate the bank names for each row, which will allow you to use the pivot table to summarise the data.

This should take less than 60 seconds to do.

1. Insert a column in the first column. This will now be a blank column A and all your data will be moved one column to the right.

2. Put a filter on column B and filter BANK* . This should filter only the rows that start with the word "BANK" in column B.

3. We need to copy the visible cells from Column B with the word "BANK xyz" to the adjecent cell in column A. One way of doing this is to put the following formula =B2 in cell A2 and dragging the formula to the last line of data or double clicking the handler in the bottom right corner of the cell to be copied. Turn the filter off once completed.

4. Almost there. Now we need to populate the empty cells in Column A with the Bank information, which was copied in point 3. One way of doing this is to select the empty cells:
a. Make a selection on column A from cells A2 to the last row of data.
b. Press the following combination of keys: CTRL+G, ALT+S, ALT+K, ENTER (or click OK). If done correctly you should have highlighted in column A all empty cells. Good.
c. With all empty cells still highlighted type "=" and press the UP arrow and then press CTRL+ENTER. All empty cells in column A should have values copied from the cells above them.

5. Now all that needs to be done is copy and paste values for column A and your data table is ready for using in a pivot table.

Much quicker to do than to write.

Hope it helps.