Extracting data in Excel and transposing/formatting it in another sheet [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 10 years ago.

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

I am new to VBA and Excel macros. I want to do the following things with a spreadsheet having 13 columns and 1000 rows.

1. Determine the unique items in Columns A to J
2. Move the unique items to a new sheet
3. Transpose the data on the new sheet 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 columns B through 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

---

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:

https://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.

---

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.

---

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

Example: For an entire row,

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