Can this be done using excel macos?

Question

Hi Guys,

I am new here and have been refered to this site by someone who thinks its the best site for programmers. I also think it is!!

I am new to Vb and excel Macros. I want to do the following things with a 13 column and 1000 rows spreadsheet.

1. Determine the Unique items on Column A to J
2. Move the Unique items to a new Sheet
3. Transpose the data on the newsheet from Columns to rows
4. Space the Unique items(the rows would be determined by the data to be fixed beneath which varies)
5. Sort the Raw Data based on unique items from Column B,C,D,E,F,G,H,I and J
6. and populate the New sheet created earlier by using the unique items and data just after the column with the Unique item

7. Create a subtotal for each populated data here is a format below

   a b c d e f g........l m

1 Mike mazer Male White London SE Barman 36 4.52

I would appreciate any response. Thanks

Answer 1

Your question has a lot of parts. It might be easiest if you broke up those parts and just asked about the things giving you difficulty.

Although, If you can already handle this problem manually, I would suggest just using excel to record a macro (you can find this function on the Visual Basic toolbar) of you running through all the steps and then opening the macro in the vba editor to tweak the code later.

Of course if you get stuck on any of the intermediate steps, you can always ask another question. People here are glad to help. Hope that helps a little.

Answer 2

Yes that can be done with Excel Macros recording common Excel commands. I'd suggest getting familiar with the Macro recorder, and then heading over to superuser.com to learn any Excel commands that you might need to record.

steps 1-2 could be done with the following:

http://superuser.com/questions/49614/excel-get-distinct-values-in-column

I've personally recorded a lot of excel commands just to see what VB code it would spit out. its a good way to learn VB

it looks like you'd need to "pivot" the data for step 3 (read up on excel pivot tables)

for 7, look into the Data>SubTotal command. It has a few different ways to configure it.

Answer 3

You can use the Application.WorksheetFunction.Transpose() function to flip the values.

Example: For an entire row,

Application.WorksheetFunction.Transpose("A1":"A1".End(xlToLeft))