Comparing And Sorting Multiple Column Groups?
Feb 28, 2014
I'm am trying to take multiple column-groups and sort them by product ID number.
I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.
I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".
starting point.xlsx
end product.xlsx
View 1 Replies
ADVERTISEMENT
Mar 2, 2007
Here's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
View 9 Replies
View Related
Jan 1, 2010
I am trying to solve is as follows, I have groups of 20 numbers in columns, one per column and would like to compare the groups to determine if any group is duplicated on another line.
I had been looking at using VBA to copy the numbers to an array, sort from lowest to highest, concatenate and use that to compare each group. If possible however if there is a suitable formula it would be preferable.
I have attached a small sample to illustrate.
View 8 Replies
View Related
Feb 18, 2009
I have a column of Cells.
Each cell has five, two digit number pairs with spaces between the pairs.
IE: 25 03 48 19 33
How can I get those number sets to sort numerically on the first digit, and still maintain the two digit grouping?
View 14 Replies
View Related
May 20, 2014
I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
[Code]....
View 1 Replies
View Related
Jan 8, 2013
I am looking to sort this file by "start date", from oldest to newest start date.
There is 3 groups, each of them is for 1 Student, I don't want the data inside the group files to be sorted, I want the whole groups to be sorted according to there "title" start date (there title is the writing with bold character and thick borders)
My problem raise is that I can not attach each title to its actual block/group.
The result I'm looking for is being able to sort (while groups are all collapsed/minimized) the groups according to there start dates that is shown in the always visible title, once sorted the whole groups will move along with there titles to the right order.
View 1 Replies
View Related
Mar 30, 2007
I realize that the easier resolve to this request is to use Access & we are moving to that application.
I have included a worksheet as an example.
We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.
Not a normal sort because the values aren't entered in typical side-by-side row & column format.
View 10 Replies
View Related
Oct 13, 2006
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
View 4 Replies
View Related
Aug 13, 2009
I have a column of different "finish" times (hh:mm:ss). I would like to have the adjacent column look at all of the "finish times" and post their ranking from 1st place to last.
View 9 Replies
View Related
Mar 10, 2009
I'm trying to merge two or more tables.
The first column of each table is the same field, for example 'Country'. Lets say the first table has information on male population, the second table has information on female population. So i want to merge the tables into one, but here's the problem: table1 has 100 rows (countries), table2 has 96 rows (countries). I need excel to recognise the 4 missing rows of data in table2 and insert blank rows so all the data in table2 corresponds to the correct country in table1 (column1).
Here's a (very) simple example: ...
View 14 Replies
View Related
Mar 13, 2007
the system works by whoever has the most points wins.. i already have my formulas and everything done..
i want the excel program to move the entire row, if possible, by whoever has the most points to the top..
(i.e If ryan d. has the most points he will be is vince v.'s spot)
can this be done automatically in excel or do i have to do it by hand?
View 9 Replies
View Related
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
May 9, 2008
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
View 2 Replies
View Related
Mar 19, 2009
I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.
This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)
View 6 Replies
View Related
Dec 13, 2008
I have work sheet, ("Sheet1"), there are about 17000 plus entries in two columns A and B,. In Column C, following formula is inserted to show the "number of occurrences" of a Group in Column A.
=IF(ISBLANK(F4),"", IF(COUNTIF($F$2:$F$118,F4)=1, "", COUNTIF($F$2:$F$118,F4) & " occurances"))
Mine active worksheet is Sheet2. In Column A, an entry is made and Column B fetches the equivalent entry from Column Sheet1:B. The formula is :
=IF(ISNUMBER(MATCH(A1, Sheet1!$A:$A,0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$A:$A, 0), 2),
IF(ISNUMBER(MATCH(A1, Sheet1!$B:$B, 0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$B:$B, 0), 1),""))
When an entry will be made in Column A of Sheet 2, naturally it brings the equal of Column B. What I will like that in the subsequent columns, C,D,E,F,G,H, etc. the values may be reflected for all the occurences equal to entered in A1. For example if in Sheet 1, :
1234 xyzxyz
1234 bxyxdx
1234 ffffffff
1234 ggggg
So these are four occurences. in Sheet2, when one entry of 1234 will be made it will show any of the four values, the rest three values may be reflected in subsequent columns.
View 7 Replies
View Related
Aug 22, 2014
Attached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.
I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E
View 6 Replies
View Related
Jul 26, 2012
I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:
KEY:
MS = Class CO = Company PLT = Platoon SQD = Squad
A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)
A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)
F14 is the average of all PT Scores
A
B
C
D
E
F
1
NAME
MS
CO
PLT
SQD
PT SCORE
[Code] ....
I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.
A
B
C
D
E
F
G
H
I
J
K
1
MS 1
MS 2
A
A/1
A/2
A/1/1
A/1/2
A/1/3
A/2/1
A/2/2
A/2/3
[Code] .......
View 2 Replies
View Related
Sep 13, 2013
I have data in Columns A to P
Column B contains specific groups.
I need to show the following:
For each group what is the count in column P that is
View 5 Replies
View Related
Mar 9, 2009
How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):
GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I
I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):
1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls
View 5 Replies
View Related
Jun 26, 2008
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................
View 2 Replies
View Related
Apr 13, 2009
I'm trying to do is develop a formula to:
- look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X
View 6 Replies
View Related
Aug 23, 2009
I need to figure out how I can get excel to write me a list of permutations / combinations for the Group (column a) and Item (column b) columns in the example. The group designates the items that should be inlcuded in the combination / permutation in Related Items (column c). I have thousands of lines to do this with so I need to figure a way to automate it. I have started the Related Items column which is the results I need output. If it could format it like this adding in the space between the rows as it outputs the answer that would be perfect. Anyone know how to do this? I've found many answers, but none that have 2 columns with separate grouping within the column.
View 4 Replies
View Related
Jul 1, 2008
I have a data set in an Excel spreadsheet entered as one column of data. It is names with addresses, phone numbers, job title, etc. I want to select and transpose each person in the list so when I am done the person's name is in column A, Company name in column B and so on. The problem is the information listed is not the same for each person - so there is a different number of rows for each person. The names are in bold text though, so I need to select from one cell with bold text to the row BEFORE the next row with bold text and transpose the data for each selection. I found this forum by finding an old question here that is similar at Copy based on Bold Paste-transpose provided in that post and it produced no result.
View 4 Replies
View Related
Apr 10, 2008
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst)
2. Copy their system info to each respective worksheet
I've attached a sample workbook.
View 2 Replies
View Related
Feb 25, 2014
I'm looking to do some simple maths with ranges in Excel but I'd like a way to be able to input all these formulas automatically although I am pretty sure a single formula per column could do it all automatically. I've got to put the sums in AD to AF on the first line of each location, but the maths is for the entire location's range.
[URL]
View 3 Replies
View Related
Feb 15, 2010
In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?
View 2 Replies
View Related
May 6, 2008
This is my first post but I have been using Ozgrid for awhile now. I am farily good with excel formulas but have just started with macros so bear with me if i dont understand what you mean at first.
I am looking for a way to copy rows our of sheet2 in the attached sheet based on the value in the segments column in sheet 2. The rows need to be paste into sheet3 (already has heading set up). The segments value is the number of times i need each row copied into the next sheet. The purpose of this is to split random length samples into 10cm incriments for study. For example, a 1.5m sample is taken so there should be 15 segments of 10cm each copied into sheet3.
Also, if possible, it would be nice for it to display the actual length of the segment after copied into sheet3 for cases where the length was not evenly divisible by 10. I have found several examples of row copying macros, but none that will copy a conditional number of hte same row based on a cell value. In the original data there are close to 4000 rows, but the number of rows will vary depending on the data source.
Another thought I had was if the total number of available rows is going to be exceded would it be possible to have the rows pasted into different sheets based on the rock type listed in the column?
View 9 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
Feb 11, 2009
I have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
View 2 Replies
View Related
Dec 22, 2006
I have data in column A which is the correct data. I also have data in other columns that I would like to compare to column A. If the data is the same, then it moves beside it, if not then it leaves a blank cell.
I have looked for macros and formulas to do this for ages but i cant seem to find one. Here is an example
BEFORE
A B C D
1 1 2 1
2 4 3 2
3 5 4 4
4 7 5 6
5 - - 7
6 - - -
7 - - -
AFTER
A B C D
1 1 - 1
2 - 2 2
3 - 3 -
4 4 4 4
5 5 5 -
6 - - 6
7 7 - 7
View 9 Replies
View Related
