Create Multiple Rows Per List, One To Many
Dec 21, 2006
I'm trying to do. I have a sheet that gets popluated and once populate needs to create a one to many output sheet. See example. Sheet1 (gets populated from another process):
1
2
3
4
Sheet2 (is my output sheet, which I then save into a text file to be imported into a different application):
11111
12222
13333
14444
21111
22222
23333
24444
and so on...
Although there is other stuff that I'm doing, this is the jist of it.
View 4 Replies
ADVERTISEMENT
Jul 28, 2014
I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
View 12 Replies
View Related
Nov 7, 2008
Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?
View 2 Replies
View Related
Mar 5, 2014
I got an employee list from couple of Site in different sheets it contains Employee name & Department but not a continued list, i.e.
on the attached file, i am looking to populate Summary Sheet, no of Sites, deparments & agents varies.
I want the Site, Department & Agent Name to Populate into one continues list without empty cells .
View 4 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Aug 19, 2009
I have data contained in matching cells from several sheets. I need a worksheet that reflect the summary of the data from the worksheets. No formula is required. I just want my summary worksheet to list the all the data from my other worksheets. I might end up having about a hundred worksheets that I need included in the summary. Attached is a sample. It would be better if the summary automaticaaly updates as new worksheets are added.
View 3 Replies
View Related
Feb 18, 2013
I have 6 macros recorded and I need to create a drop down list of macros so that I know what kind of Macro I am using.
View 9 Replies
View Related
Oct 11, 2007
I'm simply trying to take for example cell A1 from multiple sheets and list them vertically on a master sheet. When you drag it down it does not alter the sheet number, that just stays static.
View 3 Replies
View Related
Mar 6, 2008
I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:
IBM US 3 C60 Equity
IBM US 3 P60 Equity
UKX 3 P5000 Index
UKX 3 C5000 Index
I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.
----CODE----........................
View 8 Replies
View Related
Jul 26, 2013
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
View 2 Replies
View Related
Sep 2, 2007
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
View 9 Replies
View Related
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
Apr 21, 2014
I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.
Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.
Example.xlsx
View 4 Replies
View Related
Oct 21, 2009
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
View 9 Replies
View Related
May 20, 2014
I am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'.
-If the account has Medicare and Medicaid HMO financial class, then it is a '6'.
-Otherwise, it is a '0'.
View 3 Replies
View Related
Jan 17, 2014
I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
Test_Meal_Plan.xlsx
View 2 Replies
View Related
Jul 4, 2013
I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.
I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.
When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.
Is there any way to have a three column dependent drop list that can be used in more than one row?
I have uploaded the file here: ownCloud
The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.
View 1 Replies
View Related
Feb 4, 2013
I have a drop down list, using data validation, calling from a range name on a different sheet($1). Works great. I see my list of choices.
What I want to do next is for another named range on another sheet($2) to be called when I select one of the options from the drop down.
So say the drop down is in I71, when I pick an option from that list it will populate (B72, B73:G73, B74:G74...etc...) from a named range I have already defined on ($2).
Where would I put the (if I71=Range1, then paste this info into the (B72, B73:G73, B74:G74...etc...) on $1 ?
View 3 Replies
View Related
Jul 4, 2014
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
View 6 Replies
View Related
Aug 20, 2014
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
View 4 Replies
View Related
May 17, 2013
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Feb 25, 2014
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.
View 8 Replies
View Related
May 23, 2009
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
View 6 Replies
View Related
May 27, 2014
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
View 8 Replies
View Related
Mar 17, 2007
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies
View Related
Jan 4, 2013
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx
View 6 Replies
View Related
Feb 6, 2014
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
View 4 Replies
View Related
Apr 5, 2014
I need to create a mailing list for people located in a specific area from a master list.
The address data is not in great shape so I need to check address field 2 and address field 3 in the list to see if the suburb in the location tab appears in either of the two fields.
Desired result appears in column F of the List tab of the attached spreadsheet.
seminar addresses.xlsx
View 2 Replies
View Related
Nov 28, 2007
I have a list of data which looks like this
Column B
Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
View 9 Replies
View Related
