VBA newbie... need help with taking user input from worksheet and creating new table

Hi all,

I'm just starting to learn VBA and hope that you can serve as resources for what I need to do now and in the future.

I've attached a sample file to talk through this problem.

I have some user inputs of "NAME," various "SIZES" and "PRICES". For 1 NAME, there are many corresponding sizes/prices. The idea is that the user would type all of this in by him/herself. Then, press a button and have a new table created, as shown in the example. The table is sorted by Sizes.

To make the new table, I wanted to first grab all of the possible values in the size column, sort, then get rid of duplicates. After that, make a lookup table of some sort. I'm just not sure how to go about doing this.

Thanks!

example.xlsx

that looks great!

i was wondering if adding the following would be possible:

instead of only the column SIZE, let's say there's another options column, OPTIONS with strings instead of numbers, i.e. A, B, C.

The new user inputs would be like

SIZE OPTIONS
3 A
4
5 B

And I want the pivot tables to still sort by Sizes, but for those with OPTIONS, show up as 2, 3, 3A, 4, 5, 5B, and so on.

How difficult would that be?

thanks again.

Originally Posted by tehneXus

like this? Attachment 248054

yea, sort of. but I really want the final pivot table to actually have the strings 2, 3, 3A, 4, 4A, etc. The reason I wanted to do this is because in another future step, I want to plot 2, 3, 3A, 4, 4A, etc, as individual separate bars. By separating NAME and OPTION in the pivot table, it makes it harder to graph in the end, right? just wanted to see if both NAME + OPTION can be treated as a string.

Originally Posted by tehneXus

Clarification: is it Name + Options or Size + Options?

You won't be able to merge 2 columns into 1 in a pivot table, do you really need this? how should the graph look like?

oh, sorry, yes - it is Size + Options.

and unfortunately, I do need this. they're just eventually going to become portions of some bar graphs, where size+option actually becomes one of the variables I want to look at.

maybe there is some macro to write, to pre-populate the original table prior to making the pivot table? grab all the info to column Size and column Option and merge them together into a separate table, then make a pivot table? would that work?

Thanks a lot! I am just learning this VBA stuff, and it's pretty cool. I've pretty much only used Matlab the past 10 years... I didn't even know Excel could be that powerful.