Excel Sheet That Will Separate List Into Small Groups
Aug 24, 2013
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsxā€ˇ
View 2 Replies
ADVERTISEMENT
May 12, 2009
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
View 2 Replies
View Related
Oct 2, 2009
I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.
I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - DUPLICATES - Many Macros.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA29=ABCD29 PN3215 301SOUTHERN APPROACH9107 313NORTHERN APPROACH9105 322GENERAL UNDERSIDE OF WESTERN STONE ARCH9111 331WESTERN ELEVATION9104 343GENERAL UNDERSIDE9110 35 36 PN7583 371SOUTHERN APPROACH9111 382NORTHERN APPROACH9112 391GENERAL UNDERSIDE9113 Sheet 9 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Oct 10, 2007
I have a data sheet of 190,000 lines. It is a telecommunications bill with has approx 15 columns.
I need to separate the spreed sheet into the respective "call types" and put this data onto separate tabs, at the moment I do it all manually with Excel 2007.
Is there a way of automating this separation of data
View 9 Replies
View Related
Feb 19, 2014
I need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code:
Function parseNum(strSearch As String) As String
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
[Code]....
View 9 Replies
View Related
Jun 14, 2013
Running Excel 2003. I have a list in column B, of numerical codes. What I want to do is find the duplicates, and list the duplicates in column D. Is this possible?
View 11 Replies
View Related
Mar 18, 2014
I am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies
View Related
Jul 13, 2006
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
View 3 Replies
View Related
Dec 2, 2011
I have a column, lets call it A, with a small list of airports. There will never be more than 7 airports in this list. There may be as few as one airport.
Now, I have column B which has ~100 airports listed.
All involved airports will be listed by their 4-letter code, i.e. PANC, PADG, PABE, and so on.
What I want to do is, if any airport in Col A is also in the list in Col B, to have that airport populate into a cell, like C1. Now, if two airports in A are also in B, then those airports should populate into C1 and C2, and so on for 3 or more airports.
I could probably do this by nesting 7 IF formulas to check each line one by one but, there's got to be a more elegant solution that can handle this?
If VBA is needed, that's fine too as this project already has macros in it.
View 1 Replies
View Related
Dec 14, 2011
I don't have Adobe Acrobat (just the reader) and I have Excel 2010.
I'd like a macro to do the following:
Take two ranges from a single sheet.
Print them to the same PDF with different header/footers using the built-in Excel PDF generator (File->Save & Send->Create PDF/XPS Document).
How can I do this? I already have a macro which exports the current sheet to a PDF, but I'm looking to export two ranges with difference header/footers to a single PDF.
Below is my current macro:
Sub PrintActiveSheetToPDF()
Dim strFileName As String, strDir As String, strFullPath As String
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("scripting.filesystemobject")
[Code] ........
View 1 Replies
View Related
Jan 7, 2014
I have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
View 5 Replies
View Related
Apr 30, 2012
I am using Excel 2010 and need to get the totals for each month transfered to a separate sheet. For example;
One sheet is called Outgoings (which has all the receipts entered chronologically in rows and apportioned in the columns) and the other sheet tracks the cost per month of each column.
I found a formula online and adapted it to this;
=SUMPRODUCT((Outgoings!$A$5:$A$1000>=41000)*(Outgoings!$A$5:$A$1000
View 4 Replies
View Related
May 24, 2014
I have 30 xlsx files in a folder and I want the first sheet of all that files to be merged to a new workbook. The thing is I don't want the macro to copy paste the value in to the same sheet of the new master sheet like Ron's excel merge tool does. I want a macro to create new 30 sheets on the master file and copy the data from source files. And I want the newly added sheets to be renamed as the source file name. This works well except the sheet renaming thing.
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code].....
View 1 Replies
View Related
Oct 17, 2011
I have sales data in columns, the right most column has values in it.
If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.
How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?
View 5 Replies
View Related
Jul 21, 2006
I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View 7 Replies
View Related
Jun 6, 2009
I need to protect the whole sheet except for a small area where data can be entered and if possible make it so that the work sheet can be sorted. I have attached an example sheet.
View 2 Replies
View Related
Oct 20, 2012
Looking for a way to insert small photos into a excel spreadsheet.Would it work with data validation or even a lookup table.The photos need to be inserted every 8th row.Is it best to name each photo?I`m not started yet as i not sure if this is even a possibility but if there are ways i will make an attempt...Maybe a VBA code could automatic insert them when a button is selected..they are 2 columns x 4 rows in size the photos
View 3 Replies
View Related
Dec 27, 2012
So I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
View 4 Replies
View Related
Jan 8, 2010
We run a fairly small start-up company and I would'nt mind knowing if it was possible to have some kind of stock control system on 'worksheet1'. On 'worksheet2' have some kind of 'Till Style' GUI interface where you can input the products the customer purchased. And then maybe of 'Worksheet3' have a printable recipet which you can hand to the customer.
Of course the idea would be for what-ever is purshased via the 'Till-Style' GUI on 'worksheet2' to alter the stock QTY on 'worksheet1'.
View 9 Replies
View Related
Dec 12, 2012
I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like
A 6
B 3
D 5
F 1
In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.
sample.xlsxā€ˇ
View 4 Replies
View Related
Aug 31, 2007
Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
View 13 Replies
View Related
Dec 4, 2009
I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
View 4 Replies
View Related
May 21, 2013
I have SEVEN numbers and FOUR groups.
What I would like to do is list ALL the PERMUTATIONS of those SEVEN numbers in FOUR groups.
So basically, the groups would look like...
7000
4210
4201
4111
4003
3400
3310
3301
3301
3220
[code].....
...and EACH group would add up to SEVEN etc.
I think there are a total of 840 different PERMUTATIONS available, from anything from 0 numbers in a group to 7 numbers in a group.
I need ALL these groups listed individually .Is there some code that will do this?
View 4 Replies
View Related
Jun 26, 2013
I have a file that has been produced using Statistica software. The file is supposed to have around one million rows, and when exported to Excel the file is about 30 MB. When I open the .xlsx file in Excel 2010 only two pages of data appear. The rows also have weird numbering. The first square of the A column is A1. The second is A3833, the third is A6789, then A8161, then A8162, then A8163, then A8164, then A18070.
The scroll button to the left of the screen is long, as if the document was only two pages long. When the scroll button is click-and-dragged a small beige square appears with a row number. This small square seems to be aware that not all rows are shown as it shows many more rows than are visible to me.
View 3 Replies
View Related
Nov 2, 2011
I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.
I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.
View 3 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Apr 13, 2014
I have 1000s of rows of data wherein there is no space or full stop once the sentence ends and a new one starts. For example,
Original text: Bob is a boyJanice is a girl.
What I want: Bob is a boy. Janice is a girl.
View 9 Replies
View Related
Mar 10, 2014
I need to distribute a list of data into 10 even groups of data. Here is the scenario.
I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria
- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible
View 6 Replies
View Related
Feb 24, 2010
Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.
combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?
View 8 Replies
View Related
Mar 19, 2007
I have an excel workbook I'm working on, and it needs to be protected. I have some groups that can be expanded or hidden, but once the sheet is protected you can no longer use the little button to hide or expand the group.
View 9 Replies
View Related
