Groups Columns Automatically

Jan 28, 2010

Is there a macro which would group columns automatically

View 2 Replies


Transpose Automatically And Number Groups

Sep 14, 2009

1) I need to transpose about 2000 rows of 1 to 7 columns (A to G) of text/letter combinations of 1 to 5 characters into one column, each row under a group name (groups have one to three rows, so there's about 900 groups). The group name is in a row by itself, members are in the 1, 2, or 3 rows right underneath it. 2) Then I need to number the groups. There are no absolute common letter or number combinations within a group, so I can't use the first, mid, or last 2 or 3 characters to signify a common group. I do know how to use the copy and transpose feature, but would like to automate, if possible since there's so many. And automate the numbering task as well.

An example of the data is:


View 9 Replies View Related

Copy Multiple Columns Groups To 1 Group Of X Columns

Aug 6, 2009


The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............

View 7 Replies View Related

Macro To Automatically Sort Multiple Column Groups Separately As Data Is Entered

May 20, 2014

I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.

Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then


View 1 Replies View Related

Re-ordering Some Columns (groups Of Columns)

Nov 22, 2008

I must put in descendent order some columns, but the thing is a bit more complicated...
As my english is not very good i am gonna explain it with a simple example:

1 4 2 5 rabit fox dog cat
2 6 1 3 bird fis wolf mouse

Ok, i must order the numbers in a ascendent way, but reordering one number means also reordering its correspondient name (the name of the animals at its right).
So, again, with an example you will see it better:

Ordering the first row it must appear like this:

1 2 4 5 rabit dog fox cat

I didnt know if i had to post it here or in other subforum and either if it can be done with excel.

View 11 Replies View Related

How To Align Groups Of Columns

Jun 3, 2014

I would like to align a group of columns against another group of columns.

Columns G-L will not be changing. They have a certain amount of info I want to align with columns A-F

There are 30000 items in G-L and only around 3000 so far in A-F

Weekly I will import and reorder A-F and then want to realign them with G-L

This is a link to a sample of the original file: [URL] .....

This a link to how I wan the order to be : [URL] .....

As you can see there are many more rows in columns G-L

I will update columns A-F weekly and then want to realign G-L

View 2 Replies View Related

Using ScrollBar To Hide Groups Of Columns?

Mar 17, 2013

I've got a large spreadsheet with many cells that need user input. A bunch of calculations are performed on subsequent worksheets, but I want the user to only see the fields they need to enter. I'm trying to write some code that will use a scrollbar (form control, not activex) to only show one group of columns at a time. For example, if ScrollBar1.Value = 1, then show columns "A:D" and hide colums "E:Z". If ScrollBar1.Value = 2, show colums "E:H" but hide columns "A:D" and "I:Z", etc.

Here's the code I'm trying to get going, but I keep getting various errors when I try to execute.

Private Sub ScrollBar1_Change()
Dim v As Integer
v = ScrollBar1.Value


As it stands now, when I click the scrollbar I get the error: "Compile error. Method or data member not found," and the ".Value" in Line 3 is highlighted.

View 4 Replies View Related

Finding Unique Groups Of Values In Columns?

Dec 17, 2013

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

View 14 Replies View Related

Count Groups Of Alternating Rows Across 3 Columns?

Jan 18, 2014

I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.

1Column 1Column 2Column 3

View 4 Replies View Related

Identify Values Specific To Defined Groups Of Columns?

Sep 2, 2013

I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:

[Code] .........

For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.

What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)

View 1 Replies View Related

Convert/Transpose Multiple Groups Of Rows Across Columns

Mar 9, 2009

How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):


I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):

1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM

As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls

View 5 Replies View Related

Sorting Multi-rows & Columns Of Data In Groups

Mar 30, 2007

I realize that the easier resolve to this request is to use Access & we are moving to that application.

I have included a worksheet as an example.

We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.

Not a normal sort because the values aren't entered in typical side-by-side row & column format.

View 10 Replies View Related

Excel 2007 :: Automatically Transfer Columns From One Worksheet To Another In Multiple Columns?

May 4, 2013

I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..

Excel 2007


View 9 Replies View Related

Text To Columns Automatically

Jul 11, 2008

problem i am having is lets say in cell A1 i put this text:

Andy,64,8,12 (which is taken from a drop down list)

Then convert it to columns in rows B&C

When i change the text in A1 to Something else from the drop down menu, the text in rows B&C does not update and stays as ANDY,64,8,12 instead of updating to the new text entered in A1.

how to get the text in rows B&C to update automatically with row A, If it can be done it would hit the nail right on the head as this is holding me back big time.

View 9 Replies View Related

Automatically Hide X Columns

Aug 14, 2008

I am trying to let Excel automatically hide unneeded columns. A brief description:
In this sheet one should enter a number of "projects" into cell DW7 (7,127). The maximum number of projects is 60. Each project consists of 2 columns (with already prepared formats and functions). The projects are numbered from 1 to 60 (the actual project number is in Cell G6 (6,7), I6 (6,9)...).
For this purpose I wrote the following

Sub worksheet_calculate() 'Hide columns that are not needed
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
i = 1
For r = 6 To 126
If Cells(6, r + i) > Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = True
End If

If Cells(6, r + i) <= Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = False
End If..................

View 7 Replies View Related

Automatically Add Blank Columns

May 28, 2007

I import a spreadsheet each month that summarizes expenses. The columns are the months: Col B=Jan, Col C=Feb, etc. I need to add a blank column between each month. Next month the import will have: Jan, Feb, Mar. Then the next will have Jan, Feb, Mar, Apr. macro that would add the blank column between months. Since the spreadsheet columns are growing each month the macro would have to be able to count how many columns to insert.

View 6 Replies View Related

Rearrange Selected Columns Automatically

Jun 21, 2013

Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.

I have the file with various columns, such are.

Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name

The above column order are not constant, daily the order will changes. But we have to make the order as below

Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid

Like this we have so many columns in the file and we used to rearrange as required.

Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.

View 2 Replies View Related

Repeat A Formula Automatically Every Seven Columns

Feb 19, 2010

I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?

My formula is in cell K7 is:


Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:

and then to automatically continue like that for cells Y7, AF7, AM7, and so on.

View 6 Replies View Related

Highlighting Filtered Columns - Automatically

Jan 23, 2009

I have been trying to find a way to highlight columns that are filtered in Excel. On a laptop it is not always that easy to tell which columns are filtered. I have found a piece of VBA that can do this (see

I would like to do two things:

1. Have this run whenever an autofilter is changed (a new filter chosen or a current filter changed).

2. Have this effective for every workbook that I open.

I'm hoping that someone can help a VBA newbie sort this out.

View 9 Replies View Related

Automatically Create Csv Files From Columns

Apr 9, 2008

I have an excel worksheet with data from A to T and the numbers of rows may change from use to use. I want to create a button/macro that determines where the data ends and will take the data from two columns, say column A and column D and put it in a new csv file.

In my final product, I would really like to be able to have a set of check boxes that say "Column D", "Column E", ... "Column T" and so if I check TWO boxes, say "Column D" and "Column T" and click create, it will output TWO csv files, one with Column A and Column D and the other with Column A and Column T.

View 5 Replies View Related

Automatically Sort Columns Simultaneously

Apr 21, 2008

there are two colums, one for product name (unique names) and other one is for their prices (may be same for more then one product). I want to arrange these columns by the their price vlaues in assesnding/desending order and to have their unique corresponding product names in respective columns. Provide me any solution, probably using any excel formulas. presently i have tried this using Large and Vlookup funstions...but it fails.. when there are same price is available for two product names, then it takes only first one. I WANT TO GET THIS DONE AUTOMATICALLY, AS THIS IS TO BE POPULATED ON SOME OTHER SHEET AS A PARETO. waiting for your vlauable responces ASAP.

View 2 Replies View Related

Automatically Auto-Fit Visible Columns Only

May 9, 2008

In the code below, I found out how to make the column widths automatically adjust on a worksheet based on the text entered. This code works great but it also reveals the hidden columns on the worksheet. Can i prevent it from doing that?

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Cells
End With
End Sub

Found here: Automatically Adjust Row Height & Column Width

View 3 Replies View Related

Automatically Delete (or Hide) Columns In Spreadsheet?

May 15, 2014

how to automatically delete (or hide) columns in a spreadsheet given a list of columns that I want to retain (or see).

These are the columns I want to retain (or see):

1st Phone Number
2nd Phone
Email Address
Enrollment Status
Room not available
Mailing address
Payment Record
Payment Status
Requested room type
Total Payments to Date
What is your meal preference?

The attached has two tabs......columns I want to retain (or see) and the raw data of all the columns.

View 1 Replies View Related

Compare The Columns And Pick The Shortest One Automatically

Jan 7, 2009

I have two to four columns with this content: w2s3esus who translated means 9 chars (the number before the letter is the time it appears). I want to compare the columns and pick the shortest one automatically like so:

column 1: w3s4nusun = 12
column 2: w5s2esus = 11
column result: w5s2esus

View 4 Replies View Related

Automatically Copying 9 Rows And Converting To Columns

Dec 17, 2012

I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?



becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....

View 5 Replies View Related

Automatically Hide Columns Based On Dates?

Aug 4, 2013

I am trying to automatically hide columns based on dates. I have a set of dates ranging from the beginning of August this year until the end of December 2019. I am trying to hide all columns that are at least a day ahead of the current date. Today on 8/4/13 it would show all columns up until today along with tomorrow 8/5/13.


View 2 Replies View Related

Automatically Insert Static Date In Different Columns Of Same Row

Apr 10, 2008

I recently found on this site the macro to auto insert a static date in one column when an entry was made in the previous column.

What I have in my spreadsheet and what I would like to be able to do is if I select a particular Order Status from a drop down list, that it auto inserts the date into the respective columns.

Column F contains the various Order Status indicators ie Order Submitted, PO Raised, Delivered, and Invoice Received.
The date columns that I would like populated and which correspond to those status indicators (in order) are Column G, Column I, Column L, and Column K.
So if I pick Order Submitted, the status date should be auto populated in Column G. If I pick PO Raised, the status date should be auto populated in Column I etc.

View 5 Replies View Related

Moving Columns Automatically When Date Changes Business Days Only

Aug 7, 2012

I would like to move columns to the right as the date changes. For example, the top of the column has the current date. Within the column are about 40 rows of data. as the date changes, I would like that column to automatically move to the right and show that column with yesterday's date, and have that data associated along with it moved. I am looking for 6 months of columns to store, with the current date in the first column and subsequently moving right with the previous dates and info.

August 7, 2012 | August 6, 2012 | August 5, 2012 | August 4, 2012 | August 3, 2012
Accepted Processed Accepted
Rejected Accepted
Processed Processed
and so on

View 2 Replies View Related

Insert Columns And Automatically Changing Total Column?

Sep 25, 2013

I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)

I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.

View 1 Replies View Related

Automatically Create Columns Based On Difference Between Two Dates

May 2, 2008

I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.

ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved