Auto-Creating Tables from raw data?

Hi there,

I'm incredibly lazy and have a set of data on users interactions with various materials. What I want to do, if possible, is have a data set pasted into the spreadsheet, which then populates different tables. See the attached file to make things clearer.

The tables would have a list of names in them, as well as the figure for that data set (gold, iron etc) HOWEVER: I want excel to NOT put in names into a particular table if that particular name has 0 interactions with the material. So I cant use a vlookup, can anyone advise on how to do this?

Do you want the data on the Diamond Data worksheet cleared everytime you insert new Temp_Data or add the new data to the existing data?

Yes its new data on the nice looking sheet every time i paste data into the other sheet.

Sorry LOL so overwrite the existing data on the nice looking sheet?

Yes ideally it would over write it, but i can delete the data on the nice looking sheet each time i do the report if required.

Alrighty,

I've attached your original workbook. It now includes two macros that are dependent upon eachother.
The first is the FormatReport macro which runs the ResetReport macro and then populates the Materials Report. The other is the Reset Report macro which clears the existing report on the first worksheet in the workbook.

To run the code, make sure to enable macros when opening the workbook. Then select the Populate Report button at the top left of the Diamond Data worksheet.

To view the code Press Alt+F8 and then select the FormatReport macro, select the Step Into option.
Visual Basic will open and show you the coding for both macros. Anything that appears in green is a comment meant to help you understand the code so that you may alter it to fit your workbook.

Let me know if you have any questions!

Thanks!

HI there,

Thanks for the reply, Macro is one area where my knowledge really is lacking. I am trying to get this to work but cannot figure out how to run it. I've changed my macro secureity, but when i click the button it just makes "resize pegs" appear at the corners, as if i was editing the actual button. Doubleclicking opens visual basic script, and righ-clicking on the button doesnt make any "run macro" open appear. Please advise??

Never Mind i have figured that out now (just needed to restart after changing security options). I have a few questions, though. It seems that, wonderful as this is, it destroys the % formulas on the "diamond data" page, is there any way to prevent this? Also, theres a lot of hidden rows on the "temp_data" page that I cant unhide?

Hello there,

Also, theres a lot of hidden rows on the "temp_data" page that I cant unhide?

Add the following line of code:

Please Login or Register  to view this content.

Before the end sub and last line of code, so that the entire code look like so:

Please Login or Register  to view this content.

As for the formula what was the formula in the percentage column supposed to be?

Thanks!