I consider myself an ok Excel user and use it within a window of comfort for lots of things successfully, however todays task has me stumped and extremely frustrated... cant see the wood for the trees...
I have a spreadsheet of data collected from two sources, columns A & B from one source and columns C to E from another.
There are usernames in columns B and C.
I need to sort the entire sheet so that a username in column B is aligned with the same name in column C (and keeps the rest of the data in the row together)
I have scoured the net for hours for what seems such a simple operation to no avail.
Example:
A B C D E
data username1 username1 data data
data username2 username9 data data
data username3 blank data data
data username4 username6 data data
data username5 username2 data data
Column A has customer name, columns B,C,D has corresponding sales data for that month. Column E is blank. Column F has has customer name. Column G,H,I has the same corresponding sales data for the next month.
Although Column A and F both have customer names, there are many changes month over month with the addition and deletion of certain customers so the rows do not match up exactly. Is there a quick way to have the rows match up according to customer names in column A and F and then sort them alpabetically?
Currently my list looks like this: Jan Feb A A B C D D F E H F L G M H
I would like it to look like this: Jan Feb A A B C D D E F F G H H L M
Column A has donor name, column B has corresponding donation data for 2011. Column C has donor name, and column D has donation data for 2012. Column E has donor name, and column F has corresponding donation data for 2013.
Although Columns A and C and E all have donor names, there are many changes from year to year with the addition and deletion of certain donors so the rows do not match up exactly. Is there a quick way to have the rows match up according to donor names in column each of the three columns? Secondarily, I need to sort by donation amount from highest to lowest (it can either be the total over the three years or based on any single year.
Currently my list looks like this: 2011 2012 2013 A A A B C B D D C F E D H F I L G K M H M
I would like it to look like this: 2011 2012 2013 A A A B - B C C - - D- - E- F F F G G G H - - L - L - - M
Basically, I want to see the year over year giving for each donor. I've attached a sample for refrence. (I can't post the original due to confidentiality requirements.)
I have 3 columns of data and 30,000+ rows. Column A & B are related and column C contains some of the same values as column B but not an exact match. I'm looking for a way to sort columns A & B so that columns B & C match up and the data from column A is moved as well. For any data that has no match, a blank space should be input. Please see below.
A B C
red dog cat blue cat dog green mouse pig yellow horse kitten
A B C
blue cat cat red dog dog green mouse pig yellow horse kitten
I have a data entry sheet for attendance that shows the individuals name, date and hours attended. Each individual attendance is addedd to a new row. I need to transpose this data and display it in a report table, with the names in column A and showing hours worked for each day of that week. The formula needs to accomodate the growing data entry sheet as records are added (I'm using a date picker to change the week in the report sheet).
I've tried match, lookup and index and have confused myself thoroughly! Below is a sample of the data entry sheet (on left) and report table (on right)
******** ******************** ************************************************************************>Microsoft Excel - Book1___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)boutH1I1J1K1L1M1= ABCDEFGHIJKLM1SurnameDateHours**Surname1-Jan2-Jan3-Jan4-Jan5-Jan6-Jan7-Jan2Smith1-Jan-076**Smith63600363Jones1-Jan-076**Jones66000304Rogers1-Jan-076**Rogers60600005Hathaway1-Jan-076**Hathaway60600006Pearson1-Jan-076**Pearson60000007Wills1-Jan-076**Wills60000008Smith2-Jan-073**********9Jones2-Jan-076**********10Smith3-Jan-076**********11Rogers3-Jan-076**********12Hathaway3-Jan-076**********13Smith6-Jan-073**********14Rogers6-Jan-073**********15Rogers7-Jan-076**********16Rogers8-Jan-076**********17Rogers5-Feb-076**********18Smith6-Feb-076**********19Jones6-Feb-076**********20Rogers
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A Cell A2 = B Cell A3 = C Cell A4 = D Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C Cell A2 = D Cell A3 = E Cell A4 = A Cell A5 = B
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:
2012 2013
270806 330011
330011 350529
350529 369916
to: 2012 2013
270806 0
330011 330011
350529 350529
0 369916
So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.
I am trying to sort a long range of text that is placed horizontally in a spreadsheet. I can do it vertically with the sort function in Excel but it does not seem to work for text that is placed horizontally. Example is as below:
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000 Jung Joe $700 White Jon $100 Jones Jim $200 Jones Jan $300 Jung Joe $800 White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount Jung Joe $800 Jung Joe $700 Jones Jim $1000 Jones Jim $200 Jones Jan $300 White Jon $200 White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
Where: F13 = Employee number Column C on the pivot 156 workbook is where the employee number is based. The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service B = From C = To D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green 2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue 3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
I've located the following color sort VBA that sorts worksheets by color and it is working well. I'm trying to modify the code to sort each of the "color grouped" sheets alphabetically. That is, sort by color and then each color group sorted alphabetically.
VB: Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)
Dim i As Long Dim j As Long Dim ShtC() As Long Dim ShtN() As String Dim t, n As Long Dim lngSU As Long
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings() Dim iCTR As Integer Dim yCTR As Integer Dim zCTR As Integer
zCTR = 11 For iCTR = 12 To 23 For yCTR = 1 To 10 If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value zCTR = zCTR + 1 End If Next yCTR Next iCTR Range("AA11:AA" & zCTR).Select Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.
