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

Separate The Data From A Table Based On Certain Groups

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

Highlight Duplicates Within Separate Groups Of Data

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

Separate Data Groups To Seperate Worksheets

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

VBA Function To Extract Number Groups From String And Separate Them With Hyphen?

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

Excel 2003 :: List Duplicates In Separate Column

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

Return Cell On Separate Sheet Based On Selection From Drop Down List?

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

Search Contents Of Cells In Row From List Of Text Values On A Separate Sheet

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

Checking Contents In Small List Against Big List?

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

Excel 2010 :: Print To PDF With Separate Headers And From One Sheet

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

Excel 2007 :: Select And Separate Marked Values In The Sheet

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

Excel 2010 :: Get Totals For Each Month Transferred To Separate Sheet

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

Merging Excel Workbooks Into Master Workbook With Separate Sheet For Each File

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

No SMALL And LARGE With SUBTOTAL On Filtered List?

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

Small Business Formula For Price List

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

Protect The Whole Sheet Except For A Small Area Where Data Can Be Entered

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

Insert Small Photos Into Excel Spreadsheet?

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

Create Automatic List In Excel Sheet With Filtered Data From Another Sheet?

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

Small Scale Stock Sheet / Epos Style Spreadsheet

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

Counting Groups In A List

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

Permutations Of A List (groups Of 3)

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

Take A List Of Data And Then Subtotal The Value By Certain Groups

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

List Permutations Of FOUR Groups From SEVEN Numbers

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

Excel Only Shows Small Fraction Of Rows In Large File?

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

Grouping List Of Numbers Into 5 Equal Groups

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

Excel Macro To Copy Data From Array And Paste To Separate Sheet Paste Special Transpose

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

Add Space And Full Stop Between Consecutive Small And Capital Letters In Excel?

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

Dividing List Into Equal Groups Determined By Two Values?

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

Use Combobox ListFillRange To List Values From Two Groups Of Cells?

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

Sheet Protection Problem With Groups

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







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