Getting Excel to copy from one sheet to the other based on Category.

[RoSe13]

I have this spreadsheet and on one sheet I have a list of assets, on another sheet I have 7 tables, one for savings, checking, investments, etc. I normally have to copy over all the assets and manually sort them by each category then add them to their respective table, since this is very time consuming, I want to be able to have excel do that automatically. I know I probably have to use a macro because it would be too many rules for an "if" equation right? I did some research and found something called an if then statement, but I am a little confused. Can someone explain how I can get this to be done automatically?
If it helps there is a column on the first sheet that tells you the asset type, so I was previously just filtering the type and copying and pasting, the only thing is that its not in the same order. For example on sheet 2 (with the tables) column B lists the Banking institution and Column D lists the account number, but on sheet 1 column A lists the banking institution and column D lists the account number.

 

[katekoz]

It sounds like you may be able to use an Index function. Your description of your tables is confusing, though. Do you have one "master" table on one sheet that has many categories of data in it? Then, on one other sheet, you have 7 different tables, each table one unique category, so that all 7 tables make up all the possible categories in your "master" table?
Could you send a picture of your table headers, or maybe provide a different description of data? I don't think you need a macro to do this.
Also, are you tables actual Excel tables, or are they just lists with headers?

 

[RoSe13]

Sorry about that, the tables aren't actual tables, they are just cells with a border on them and a header. There is one master table with many categories, however those categories match the separate tables on the sheet with the 7 tables. In other words, yes, the 7 tables do make up all the possible categories in my master table.
I've attached a couple pictures one of an example of the master list, I actually copied and pasted a portion of one list and just changed the account numbers. The second picture is a portion of the second sheet with the tables. Hopefully this clarifies what I mean. I just want to have excel automatically fill in as much information as possible, best case scenario I would only have to manually fill in the account names on the second sheet.

 

[RoSe13]

It sounds like you may be able to use an Index function. Your description of your tables is confusing, though. Do you have one "master" table on one sheet that has many categories of data in it? Then, on one other sheet, you have 7 different tables, each table one unique category, so that all 7 tables make up all the possible categories in your "master" table?
Could you send a picture of your table headers, or maybe provide a different description of data? I don't think you need a macro to do this.
Also, are you tables actual Excel tables, or are they just lists with headers?

I accidentally posted to the thread instead of replying directly to you, however, I forgot to mention that an index function would not work because the master list changes from person to person, so I would have to constantly keep changing the equation, which would also be time consuming. What I need is something that would search for an account type, for example, a savings account and then fill the second sheet with the information for that savings account.
Please let me know if you have any other questions.

 

[katekoz]

I understand your setup now! I'll see if I can come up with something to help. One thing I don't understand - you said the master list "changes from person to person." What does that mean? Are there multiple people providing their own master lists, and each one is a different format??
Also, would you be opposed to turning these tables into actual Excel tables? I find that they're much more friendly to work with, especially when you have variances in formats and lengths of tables.

 

[RoSe13]

I understand your setup now! I'll see if I can come up with something to help. One thing I don't understand - you said the master list "changes from person to person." What does that mean? Are there multiple people providing their own master lists, and each one is a different format??
Also, would you be opposed to turning these tables into actual Excel tables? I find that they're much more friendly to work with, especially when you have variances in formats and lengths of tables.

Yes, there are multiple people providing their own list of assets. I would not be opposed to turning it into Excel tables at all, anything that can help me be more productive. Thanks so much!

 

[katekoz]

Are all of the people providing lists of assets providing them in the same template (the format you provided), or are they all in different formats?

 

[katekoz]

One more question - in your small tables, where does the Account Name and Current Balance come from? I don't see those fields in your master table.
I think I have a solution worked out using formulas that could work for you. It depends on your answer to the previous questions, though.

 

[RoSe13]

One more question - in your small tables, where does the Account Name and Current Balance come from? I don't see those fields in your master table.
I think I have a solution worked out using formulas that could work for you. It depends on your answer to the previous questions, though.

Yes, all of the people provide it in the same format. The account name we have to input manually, so that's the only field we can't auto fill. The cash value is what we use to fill, current balance/average balance/account value.

 

[katekoz]

Ok, I've worked up a file that I think will do what you want it to do. Unfortunately, we can't attach files, so I'll have to give you screen shots. Let me know if some of it doesn't make sense.

Also, this is the simplest and most comprehensive way I can think of to use formulas to do what you want. That being said, a macro may do what you want better. I am, however, not good with macros! If you'd like a macro solution, I recommend you do a new post and start the title of it with "vba..." There are some AMAZING programmers on this site who can help you, but you'll need to catch their attention. I find that leading your title with "vba" is a good way to do that, as I use assistance for macros on this site often. I'll send some photos of how I recommend using formulas for your task, though.