Column List Sorting Locking 2 Columns Together

Sep 24, 2006

This should be a simple question for those who have the knowledge. I am making a 2 column excel page, the first column will have an authors name and the second one will have the book name. I need to lock these two columns together so that author name and book name always stay together (side by side) on the sort command. I need to be able to sort by author or book title and I realize that it gives you the choice to expand the selection, but I can't trust that the others (kids) will realize the importance of doing so. This is going to be a very large list with hyperlinks and I can't afford to chance whether someone else will select the correct command. So a long story short. I want to build a list that can be sorted by author name or book name and be sure that the correct author will always be beside the correct book, but that are able to be independantly sorted

View 5 Replies


ADVERTISEMENT

Locking Rows Together When Sorting

Apr 30, 2009

I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1

View 3 Replies View Related

Sorting A List Of Numbers By Multiple Columns

Oct 13, 2008

I want to sort the list like this:

1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file).
2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change.

Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.

View 2 Replies View Related

Sort Columns Without Sorting First Column?

Jun 28, 2013

I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.

View 2 Replies View Related

Variable Column Range Based On Current Selection For Sorting Columns

May 16, 2014

The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)

View 2 Replies View Related

Sorting One Giant Column Into Multiple Columns Based On Cell Color?

Sep 3, 2013

I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.

Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.

View 1 Replies View Related

Excel 2007 :: Sorting Pivot Table Columns By Column Field Label (date)

Jun 30, 2014

I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.

E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".

Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.

I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.

i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.

View 5 Replies View Related

Locking Cells On Certain Columns

Jan 24, 2013

WILL IT BE possible to lock columns / cells for any user to avoid editing? Only the creator of the file should be able to edit?

View 2 Replies View Related

Locking Columns On A Questionnaire Spreadsheet

Sep 16, 2009

I am trying to develop a questionnaire spreadsheet using a ranking system. I have different questions (based on certain criterias) and four columns for each question: NEVER | SOMETIMES | MOST OF THE TIME | ALWAYS.

I want to see if there is a way that I can lock the other three cells when the user has chosen a particular column by typing an X. BY doing so, I want to ensure that the user will not choose two options (or columns) instead of only one, because if he/she does it will mess the other calculations that are not shown within the table. Only the input and output are shown.

View 8 Replies View Related

Locking And Protecting Certain Cells - Filtering Columns

Jan 22, 2013

I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.

Is there a way to lock cells so that they can not be edited but also keep the filtering function?

View 2 Replies View Related

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.

The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.

What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.

I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.

I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....

If anyone has any suggestions I would be eternaly greatful.

Passwords on the sheet for the different buttons are abc or cobra.

You can download my Spreadsheet here.

[url]

View 9 Replies View Related

Excel 2010 :: Hiding And Locking Columns And Formulas Together Via Passwords?

Mar 8, 2014

Is there a way in Excel 2010 that the administrator of a excel document can not only hide some columns but lock the columns by some kind of password so it remains hidden? I know user can hide/unhide columns but can some columns be hiddens and locked with passwords?

Secondly can formulas like IF and vlookups statements be hidden and locked with some kind of password?

In maybe via VBA or by some other means, is this possible?

The end user should only be able to type and select certain cells only.

View 5 Replies View Related

Locking Column A Only

Dec 10, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub

everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a

View 4 Replies View Related

Locking Date Column

Oct 10, 2008

I have set a set of cells to calculate dates from other cells and locked it so that people can not edit the date. The date will be automatically populated. However when i lock the cell, a debug error comes up says runtime 1004. I reckoned that when the column or cell is locked from editing, it will also unable to populate or run calculations. am i right?

View 9 Replies View Related

Locking Column Width And Row Height?

Jul 22, 2007

I'm creating a map like image with Excel, and I've set the column width and row height to certain dimensions. Now I'm entering in some text into them, and I noticed that when I added in 2 digit numbers, the column width expanded a little bit. I want to prevent this from happening, and still be able to enter in the 2 digit text (since it WILL fit, and it's expanding to keep that extra bit of space).

Is there any way to lock the dimensions?

View 7 Replies View Related

Locking Row/column Headings In Place

Apr 5, 2009

The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns.

As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles.

Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?

View 2 Replies View Related

Locking/Freezing Contents Of A Column

Apr 15, 2009

I'm using Excel 2003 and was wondering if there was a way I could Lock/Freeze the contents of a column. For Example:

Cell A1 has the formula =B1+C1+E1 - figure shown is £405
Cell A2 has the formula =C2+D2+E2 - figure shown is £650

Is there anyway I can freeze column A so when I delete column B,C,D,E ect the correct figure will still show?

View 4 Replies View Related

Protecting Or Locking Cell In Certain Column

Feb 26, 2007

In Excel 2007 I protect cells in certain cloumns in a large amount worksheets. By manually protecting or locking them. Once the sheets are protected without a password just blank and only select unlocked cells is ticked for sheet protection. I close the workbook and reopen it. These cells are now unprotected that were protected and theres always two ticks checked off for select locked cells and select unlocked cells.

View 9 Replies View Related

Locking Conditional Formatting In 2000: Copy/paste Text From Other Cells Or Columns Even Other Workbooks

Jul 27, 2009

I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.

I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.

View 4 Replies View Related

Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.

View 9 Replies View Related

Distributing List Of Numbers In One Column To Several Columns

Jul 23, 2013

I'm working with a dataset from a survey that has recently taken place. The original structure of certain questions in the survey were multiple choice. The ideal structure for these questions, in terms of variables, is that each possible option for the question (A,B,C,D... etc) have a separate value, with a 1 for yes (reported) and 0 for no (not reported). In other words, if Question1 has multiple possible answers (A,B,C...), then there should not be one variable created from this question, but rather, there should be a variable for each possible response: Question1_A, Question1_B, Question1_C, and so on. And within each of these variables, should be a 1 or 0 depending on if that option was reported or not.

The format I received the data in, though, was a single variable with a list of letters, (e.g. B,D,F) for each option reported.

Variable: Question1
Row 1 A, B, C
Row 2 B, E, F
Row 3 B, D, E

The data I have right now is housed in Excel prior to exporting to SPSS and/or STATA. I need to find a way to distribute these values into separate columns (variables). The tricky part, is that if I simply use "text to columns", it distributes the letters to new columns, but doesn't take into account that the first value reported might not be A. Is there a way to take the list of values in the single columns, and distribute them to assigned newly created columns?

View 6 Replies View Related

Convert 2 Column List To Multiple Related Columns

Nov 28, 2007

I have a long list of 2 columns containing data as follows:

col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3

which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....

View 9 Replies View Related

Macro To Segregate One Column List Into Three Columns Based On Condition

Nov 21, 2012

Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.

Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.

View 4 Replies View Related

Create Column List Out Of Table (rows And Columns) While Removing Blanks

May 26, 2014

I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.

Formula:

[Code] .....

View 12 Replies View Related

Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns

Jan 23, 2006

I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.

BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34

AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12

View 14 Replies View Related

Excel 2007 :: Make One List Out Of Columns Based On Common Information In Column C?

Oct 12, 2011

Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007

column CColumn Fcolumn Hcolumn J

Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....

View 2 Replies View Related

Sorting 4 Columns Together

Nov 6, 2007

Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.

View 14 Replies View Related

Sorting When Using Columns Beyond AA

Oct 6, 2009

Im not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.

View 4 Replies View Related

Sorting Columns

Apr 13, 2007

I have a spreadsheet that is populated my Concatinating data from other worksheets. Some of the results are numbers, some is data while others are blank spaces and othersare set by the concactenating default of "" when the criteria is not met. I need to sort these colums. However it seems that the default "" from the formula is not a BLANK or a ZERO or an empty cell.

Those cells will place themselves at the top of the sort. I need to eliniate them. I have copied and pasted as values but that has no effect. Ironically I can identify the cells with an if statement so what I need help with is generating a macro that will cycle thru the range of results and delete the approriate cells.

I have tried the following:

lr = Range("A1").End(xlDown).Row ' Last Row
lc = Range("A1").End(xlToRight).Column ' Last Column
For Each cell In Range(Cells(1, 1), Cells(lc, lr))
cell.Select
AC = ActiveCell
If AC = "" Then Selection.Delete Shift:=xlUp
Next

But this does not work as it cycles top to bottom and leaves behind 1/2 the problem cells.

View 7 Replies View Related

Sorting List To A New Sheet

Feb 24, 2006

I have a database being exported to a spreadsheet with over 40 fields (columns) and 1000+ clients (rows). One of the fields (column C) incidates that referral source. I would like to copy & paste the list into sheet 1 and have 5 seperate sheets for each of the 5 different referral sources. So that when I go to sheet 2 the only thing I will see are the clients and their 40 associated fields that are referred (column C) by say "John Smith"

Is there a function or script that will search column C for "John Smith" and then fill in all the 40 fields on sheet 2.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved