is there a way to combine the info from several columns into 1 list?
All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.
This is Excel 2007
I'm trying to have a drop down list in a spreadsheet which combines the info from two columns.
For example:
A B
1 John 29
2 Sandy 40, etc
And the drop down list turns into
John 29
Sandy 40
Is this possible?
If I have a set of number in a column. a1-12345 a7-12356 and a20-15487. how can I get them to show like this in columns Z.
12345
12356
15487
I am use 2007 and my data go down to cell 40000 in column A:A
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].....
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
I have a list of names in column B, I can easily put this into a list
I also have a different list of names in Column C
These two list are feeding from sharepoint and are updated regularly
What I want to do is create a drop down list that will list all the names in Column B and Column C, while keeping the initial lists in there separate columns
I have been trying to find a formula to lookup both columns and return all the names into another column (E) using the formula below
=IFERROR(INDEX($B$2:$C$600,MOD(ROWS(E$2:E2)-1,ROWS($B$2:$C$600))+1,INT((ROWS(E$2:E2)-1)/ROWS($B$2:$C$600))+1),"")
the plan been to just put column E into my drop down list, but this formula is returning alot of blanks between Column B + C in the list (there might be 100 names in column B and only 30 in column C)
To try remove the blanks I tried another formula pulling the info into Column F,
=IFERROR(INDEX($E$2:$E$5000,AGGREGATE(15,6,(ROW($E$2:$E$5000)-ROW($E$2)+1)/($E$2:$E$5000<>""),ROWS(F$2:F2))),"")
But while this did remove the blanks (about 500 blanks) it left me with about 70 "0" errors.
I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.
I would like to merge each row with the same contact into one row.
I have attached an example : Example test.xlsx
Here is basically the problem, I have 2 sheets and want to copy/ paste them like this:
Sheet 1
Name of a product
Data 1
Data 2
Data 3
[Code]....
Can this be done in Excell 2007?
I've got problem with combining 3 cells into one. First cell contains text, second have date yyyy-mm-dd and last one is as previous. I'm using formula .
[Code] .....
And I'm getting: TEXT_41694_41701. How can that be corrected to use formatting for date format to get TEXT_yyyy-mm-dd_yyyy-mm-dd?
I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).
unique list.xls
I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
I have 3 commission workbooks all with about 20-30 worksheets in them with each one listing the sales rep and a history of commissions owed/paid. I want to create a single workbook/worksheet where I have listed all the sales rep and the amount of commission they are currently owed without have to copy/paste or type it all in one at a time. How would I go about this??
View 8 Replies View RelatedI have a data in sheet1 as below :
Excel 2012ABCDE1Sr. No.PriceSr. No.Price
21234535123453531234623123462341234712123471251234818123911861
234919135011971235023412350234812371251235125912352261235226101
2353271235327111235428123982812123552912355291312382301235630141
[Code] .........
And now looking for output in sheet2 as below :
Excel 2012ABCD1Sr. No.Price1Price2Total
21234535357031234623234641234712122451234818#N/A1861234919#N/A
1971235023423446881237125#N/A259123522626521012353272754111235428#N/A
[Code] ........
The sr.no. codes and price will keep changing in sheet1, it should automatically update in sheet2. Is it possible with either vba or formula?
Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
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
B
C
3
Name
Company
[Code].....
I have a List of Different Fruits in Cells A1 to A5
Apple
Banana
Orange
Strawberry
Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
I am building a rather good size userform. It is getting really lengthy code-wise because I want certain things to be enabled and disabled on the form as they click on certain options.
I know I can hard code the lines but so I am trying to come up with some coding elements where I will not have to add 20+ lines of code for everytime I want to add a new feature.
Here is one of the functions that I have, There is going to be several of these:
Private Sub chkMore2_Click()
If chkMore2 = True Then
cboProtocol2.BackStyle = fmBackStyleOpaque
cboApplication2.BackStyle = fmBackStyleOpaque
txtLowPort2.BackStyle = fmBackStyleOpaque
[Code] .......
So If I click chkMore2 ... all of the 2nd elements will be editable. If I click chkMore3 ... then all of the 3rd elements will be editable, etc. etc.
So I tried to play with this code example:
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Call Changeit(1, "yes")
Else
Call Changeit(1, "no")
[Code] ........
Excel 2007 does not like the .concatenate element.
I have a spreadsheet that has a long list of charges... some of those charges have multiple codes attached to them... when this happens I am wanting to group them together and let the user pick which one they want to view at one time.
Here is a very small example of what exactly I am referring to... with charge #14 I am wanting it to combine and let the user pick between 2565 and 2566. The problem is that the values can range and will not always be 2565 & 2566 and there is 10,000 + records similar to this.
test.xlsx
I am struggling with trying to create a Top 10 list in Excel 2007. I have googled and search the forum but could not find a solution. (Aplogies if i have overlooked a thread)
I have attached an example of my problem.
1. In Column F i would like to extract the names of the Top 10 performers, based on their respective score. Hence, based on the scoring in Column D, Column F should extract the Top 10 performer names from column A.
2. In Column G, same as above, but bottom 10 names
3. From the attachment, you will view some names are highlighted. I.e. Gary & Neil, and Ian & Michael. These are highlighted based on the fact that they have equal scores.
When extracting the Top / Bottom 10 list, i would like to rank the performers with same score differently. e.g. Gary & Neil, both scored 0%, hence they are equal. But in the Top 10 list, i would like to rank Gary higher as his absolute target is higher. (63 vs. 27) . Same applies to Ian & Michael, Michael should rank higher in the Bottom 10 List as his absolute target is higher.
Excel 2007 - Sorting across columns
Need to sort part of an Excel worksheet across multiple columns.
For example data is:
ABCDEF1
Machine
4394264394294394434399834399842
Model
TD860TD400TD860TD620TD6203
Pgm
OPTABCOPTEP2EP24
Start
5/31/119/1/115/1/096/15/092/1/085
End
2/28/158/31/125/31/114/30/108/31/09
I need to sort it first by Pgm, within Pgm by Model, and within Model by Machine. I do not need to sort on Start or End
The results should look like:
ABCDEF1
Machine
4394294399834399844394264394432
Model
TD400TD620TD700TD860TD8603
Pgm
ABCEP2EP2OPTOPT4
Start
9/1/116/15/092/1/085/31/115/1/095
End
8/31/124/30/108/31/092/28/155/31/11
After selecting the data to be sorted (columns B,C,D,E,F), I tried using "Sort Left to Right" under DATA-SORT-OPTIONS but it doesn't seem to work.
how to add many columns to the rightmost part of the worksheet carrying on from Col AA?
I keep getting error 'Cannot shift objects off sheet.' and followed instructions to change a setting to All or use control-6 but this made no difference.
I have the following situation and I'm trying to do this using a PC with Excel 2007.
I would like this:
T
N
O
O
[Code]....
The total number of columns will be 11, number of rows will vary.
i want to make a dropdown list from excel 2007. I try data validation then allow then list then source but i cannot make the sheet 2 as the source of my dropdown list in sheet 1. i uses excel 2007 and my OS is XP.
View 4 Replies View RelatedI am creating a form using Excel 2007/XP, and am trying to make it easy to use for both people who want to click and people who want to type. I have a series of list boxes in the form that make it easy for the clickers, but I want the typers to be able to tab into the box and make a selection. For example, if the choices are Apples, Pears, and Grapes, I would like a user to be able to tab into the box, hit P and tab to the next box and have Pear be selected.
View 2 Replies View RelatedI have cells containing large drop down lists (offering many possible entries). Is it possible to get excel to "jump" in the list or complete the entry automatically? If i start typing "aut", it should the drop down entry "automation". For your information, I just use Define Name and Data Validation to create the drop down list. Is it must be using VBA code or I just can use Define Name and Data Validation which has been I created?
View 5 Replies View RelatedI have a large table of results that looks like this...
Forename___Surname___Art___Maths___Science___English___Drama
Billy________Bob_____________B_______A________C_____________
Michael_____Micky_____A_____D_______B_________________C____
Shelly______Sholly_____A*____A________________A________B____
I am looking for a way to write this data in a list like this........
Billy__Bob__Maths___B
Billy__Bob__Science_A
Billy__Bob__English__C
Michael__Micky__Art__A
Michael__Micky__Maths__D
Michael__Micky__Science__B
Michael__Micky__Drama__C
Shelly__Sholly__Art__A*
Shelly__Sholly__Maths__A
Shelly__Sholly__English__A
Shelly__Sholly__Drama__B
Excel 2007
I want to link a table to a form. Only, I noticed that when setting the input range in the form I'm unable to use OFFSET OR a table name. How can I link the form to a list which is going to expand?
Attached is an example. Two sheets. One for the list. One for the form. The list will keep getting added to.
Unrelated-but perhaps useful - Is it possible to link one cell to a list in another sheet so a user can type in a word and it will offer autocomplete options in excel 2007 ?
linklistoform.xlsx
I am trying to create a dynamic sub-table on another sheet from a master list in excel 2007. Where the master list size can change and have this reflected in the sub-table. I cannot use pivot tables.
Example:
Master List (Locations):
London
Drummond
Kentville
Sub List (should look like this):
London #count
Drummond #count
Kentville #count
I am using the code below (Excel 2007) to delete and add back timesheets based on a list of names from the "Names" tab. In addition, I am making a "Table of Contents" with hyperlinks on a separate worksheet that will allow employees quick access to their timesheet without having to look at each tab. The code below works well if I want to delete and add back all the timesheet at one time but if we get a new employee in the middle of a pay period I have to use a single timesheet until the end of the pay period.
What I would like to know is, can the code below be modified so that when the code is run it reviews the list of names and only adds a new timesheet for that employee without deleting and adding back all the timesheets?