Formula To Reorder Data - Match/Sort/lookup
Jan 4, 2007
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
View 9 Replies
ADVERTISEMENT
Aug 13, 2014
In the attached spreadsheet my data consists of columns A through K. Essentially I want to line up the data so Column E matches column K. When no data exists in the other section, then a blank line should be inserted. The example I gave has the data formatted the way I want it. As you can see, line 11 is blank. Column L can be ignored as my manual way to handle this was to create an if statement that returned "Error" when something didn't match.
My spreadsheet has 29,000 lines in it. So doing this with my IF statement method would take enormous amounts of time.
Sample Problem.xlsx‎
View 5 Replies
View Related
Jan 2, 2007
_A_______B______C___
1-Jan___13:00___13:40
1-Jan___13:10___13:50
1-Jan___13:10___14:00
2-Jan___16:00___16:35
2-Jan___16:20___16:40
3-Jan___10:25___11:20
Is it possible to run something so that either somewhere else on this sheet or on another sheet that I can get the number rearranged like this.....
_A_______B_______C______D_______E______F
1-Jan___________2-Jan___________3-Jan
13:00___13:40___16:00___16:35___10:25___11:20
13:10___13:50___16:20___16:40
13:10___14:00
And do it as the infrmation is entered into the original form above. The number of entries for each date will vary.
View 9 Replies
View Related
Feb 26, 2009
I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.
View 3 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
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.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
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.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
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
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Jan 6, 2014
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.)
View 2 Replies
View Related
Jul 19, 2012
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
View 2 Replies
View Related
Apr 29, 2014
I've got a lot of data like this. They are paired letter/number and I want to keep it like that.
a202b520t630s630d689
a265s687b953t953d1062
a47t547b890s890b1015
However I want to re order them in pairs so that its alphabetical eg. a 202 b 520 d 689 s 630 t 630
View 3 Replies
View Related
Mar 20, 2014
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
View 3 Replies
View Related
Nov 13, 2008
=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,))
That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
View 4 Replies
View Related
Aug 19, 2006
I am wanting to do is have the values from a number of cells on sheet2 returned to sheet1. The catch for me is that I am using the validation command on sheet1. When I select an item in the validation field I want to return data that belongs with that selection. It may be best with an example....
On sheet2 I have a list of companies in Col.A, their street address in Col.B & their phone number in Col.C.
On sheet1 I have a Validation field that lets me select from the list of companies...
When a Company is selected I want to have the address data & phone numbers returned and placed in the cells beside the Validation box.
View 3 Replies
View Related
Sep 4, 2006
My database includes data about period (column A), a product code (column B), Turnover previous year (column C), Trend (column D) and Turnover this year (column E). In column C i want to have the turnovers from a particular product code from the previous year. The difficulty is:
1. that the product code can vary
2. not all periods are availabe.
How can i formulate a formulate that shows the turnover from the previous year, and in case there is no data from the previous year it should show "n.a.". This formula must also take into account that product code can vary. In my example: C3 should show 1296, C4 should show 877, C5 should show 884 and C6 should show 960. In case the turnover of period 200104 was not available C3 should show n.a.
View 3 Replies
View Related
Sep 28, 2011
determining the correct formula to lookup various details from multiple worksheet:
Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000
Sheet2: Data
AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
View 9 Replies
View Related
Feb 5, 2014
(File is attached here)
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
View 3 Replies
View Related
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Jun 10, 2009
Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.
View 10 Replies
View Related
Feb 17, 2012
If I do a simple AVERAGE or SUM of A1:A10 in worksheets Sheet 1 to Sheet 3 I do get the result I need (average or sum of this cell range across the 3 sheets).
Now if I change the order of my data (sort it) in one of the sheets, what used to be A1:A10 will perhaps become F1:F10 after sorting and the whole point of 3D reference formula will be lost. Lets say in all these sheets ROW A contained some whatever values for MILK and I needed to SUM them across 3 sheets, now A1:A10 might be BREAD (after sorting) and I will be summing MILK and BREAD.
My formulae does not know I sorted my data.
That is the question - is there a way I can sort my data in one sheet and still be able to use 3D referencing to get my totals right?
View 6 Replies
View Related
Jul 2, 2008
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.
View 9 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
Jul 8, 2014
I am looking for a formula that will add some numbers for me on different sheets based on a sorted option that I choose on my Summary tab. sample survey (1).xlsx Here is a sample workbook with what I am looking for.
View 3 Replies
View Related
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 9 Replies
View Related
Mar 4, 2010
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
View 9 Replies
View Related
Apr 22, 2009
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
View 3 Replies
View Related
Mar 27, 2009
Attached you will find the spreadshhet in question. If you click on the click here button input the following info:
8.0
3508949
80
0
0
25
the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.
View 3 Replies
View Related
Feb 24, 2010
where do i put the indirect.ext to below formula so that that Test.exe file be checked offline .. this below works only if the test.exe is open.
=IF(A2="";"";COUNTIF('C:Folder[Test 24.02.10.xls]Table1'!A$1:A$65536;A2))
Since the Test file is generated every day..
how can i make the Test file shown every day with different date.. this 24.02.10 is not permanent.. everyday changes.. meaning tomorrow it checks Test 25.02.10.exe, Friday it checks 26.02.10 etc.
I could make a cell for date input .. and from that cell it could be added up to enter into above formula automatically..
View 9 Replies
View Related
Mar 8, 2013
In sheet1, I wrote my data for lookup porpose. The data named (Table). Then I saved it as Add-in (named TEST). That Add-in, already added to my excel Add-in
I open another excel file but How can I use lookup formula from that data (Table) in sheet1 in that Add-in (Test)?
View 1 Replies
View Related
Dec 13, 2012
Refer the attached work sheet and any formula to calculate the values once the criteria is met.
To calculate % after multiple look ups.xls‎
View 7 Replies
View Related
May 28, 2013
I've a data looks like below..
Name
Yes/No
Marks
Pass/Fail
Susheel
60
Pass
Sanjay
30
Fail
Susheel
Yes
10
Pass
Mohan
21
Pass
I want to populate the data in main sheet for unique name...based on some criteria.
1- Name Should match in both file
2- If "Pass" then it Should be "Yes"
View 5 Replies
View Related
