VLookup To Sort Vertical Data
Jun 9, 2014
I have a very large Spreadsheet (time Sheet Report) that is very messy, how to reorganize the data properly.
We have employees that worked multiple shifts and our system record all their punches. However the system list the data in a vertical position, but I need it to be display horizontally. But there is another problem, there is some employees that did not punch for lunch and would like to add the 30 minutes lunch for only those that worked 8 hours shift.
Attached is a sample : Sample.xls
View 2 Replies
ADVERTISEMENT
Jan 27, 2014
I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).
The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".
I was trying something with INDEX / MATCH, but can't get it to work.
View 4 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
Jan 20, 2009
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
View 9 Replies
View Related
Sep 8, 2009
reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
The attached example shows before and after.
View 6 Replies
View Related
May 28, 2012
I'm trying to rearrange vertically aligned data so that they are horizontal. But at the same time, I need this macro at an interval.
To give you an example,
Mr. Yang Xin 221-421-5123 Male College Grad
Ms. Taylor Cindy 534-123-5512 Female Uni Grad
In this case, we need the data to be arranged this way.
Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad
Could you write me a macro?
View 5 Replies
View Related
Dec 11, 2012
I have a data which contains:
A B
1 Name: A
2 Address: NY
3 Gender:Male
4 Cell Numbers: 123
[Code].....
I know that this has been asked a million times, but I need the new table in a new sheet (which is sheet 2 and also I skip the Cell Phone numbers data).
View 2 Replies
View Related
Jul 9, 2014
I have a page of data that i need to summarise/calculate, i thought sumif would be the correct formulae but i can't get it to work...
Sheet 1 - Data Recomds Emp Name, Weeks 1-52 showing no of hours to adj
Name
WK1
WK2
Wk3
WK4
WK5
WK6
Oliver
-1.5
[Code] .....
Sheet 2 - Summary by month - to Calculate the no of hours for the period per employee
Name
Month 1
Avery
Require Sum for employee Avery Wk 1-4
[Code] ..........
View 5 Replies
View Related
Mar 7, 2013
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
View 1 Replies
View Related
Jan 19, 2012
how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?
View 3 Replies
View Related
Jan 29, 2008
I have a 27R x 19C table (1st row is headings) that I want to sort. The column that I want to sort on (column O) contains a formula that multiplies two values, each of which is obtained by a VLOOKUP formula. The formula in the cell is: VLOOKUP(E3,StrategicImpactTable,2,0) * VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0). Named ranges StrategicImpactTable and StatuteRegulatoryImpactTable each contain two columns, the second of which is a numeric value associated with the text in the first column.
On the first row VLOOKUP(E3,StrategicImpactTable,2,0) evaluates to 3, VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0) evaluates to 2, and the product results in a numeric value of 6. The 26 values in column O are 6, 6, 4, 2, 2, 4, 4, 6, 4, 4, 4, 4, 9, 9, 9, 3, 3, 9, 9, 9, 4, 9, 9, 9, 9, 9.
When I sort the table descending on column O, the table does not sort properly, with no discernable order. Why?
View 9 Replies
View Related
Dec 25, 2013
I have not found a suitable solution for arranging vertical data horizontally using VBA
I have number of data in column B arranged vertically in sheet1 how can we arrange the data in sheet 2 column B row 3 horizontally.
The vertical data keeps increasing when new data is added, so VBA code should search to the last line in Sheet1 column B.
All duplicate entries should not be repeated when arranging horizontally.
View 9 Replies
View Related
Sep 5, 2009
i have a data in columns and would like to convert into rows
e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0
and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..
View 6 Replies
View Related
Jul 20, 2012
I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.
Here's an example of the table:
___ A B C A B C A B C A B C A B C
Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
...
...
But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".
I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way.. I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria...
View 14 Replies
View Related
Apr 18, 2013
I have the attached worksheet that I would like to know how to move the data from Vertical to Horizontal
Is there a set function to do this or does it require a macro?
View 5 Replies
View Related
Dec 13, 2005
a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--|| --etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|
Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.
Is there a way to have the data from def and ghi fall in line underneath abc with the condition being that there has to be something in the cell?
View 10 Replies
View Related
Aug 20, 2008
I have one spreadsheet in which data was inserted vertically, and in order to move it to SQL, i would like to first mirror it horizontally in a 2nd spreadsheet (the reason i want to mirror it and not just copy the data is that the information in the first sheet changes dynamically).
So i figured out the basic function - (=sheet1!B0), but doing this on more than a 400 cells could be a problem. So my question is, how can i make, and lets take an example, cells A1 through I1 (in spreadsheet2) mirror A1 through A9 (in spreadsheet1), without editing functions cell by cell?
View 6 Replies
View Related
Apr 20, 2012
I need to do is to move data which is in 3 columns with multiple entries (ID) in first column to single entry in first column (ID) horizontally.
Currently three columns are ID, Code and Reply:
111 A D
111 B F
222 A I
333 B D
333 C F
What I would need is (ID, Code1, Reply1, Code2, Reply2):
111 A D B F
222 A I
333 B D C F
Is there a macro I could use for it?
View 1 Replies
View Related
Jan 16, 2009
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.
My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)
I need to be able to do this without transposing any of my data.
Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)
I'm using excel 2003 and windows XP professional.
View 9 Replies
View Related
Jan 12, 2010
How am I able to extrapolate data from vertical to columns?
I.e.
From:
ColumnA ColumnB
Field 1 Result 1
Field 2 Result 2
To:
Column A Column B Column C Column D
Row 1: Field 1 Result 1 Field 2 Result 2
This is different than Transpose.
View 10 Replies
View Related
Aug 31, 2009
I have a problem with the VLOOKUP function and i can't work out what is going on. I've attatched file. On the members sheet in cell F9, the result should equal 13.2, but the formula throws out 13.0. The final VLOOKUP seems to be the problem. The lookup value is F8(which is 13.4), the table array is named 'calc'(on the tables sheet), and column index number works out to be 3. The figure in column 3 is 13.2, yet it throws out 13.0!
View 4 Replies
View Related
Jun 13, 2013
I need to transpose or move some of the data on Column B to horizontal position, is there a formula that I can use to do this? Attached is sample of what I want to do.
View 14 Replies
View Related
May 21, 2013
Here My first thread autofill horizontally from other sheet vertical data.
Vertical Data
Sheet1
A1
A2
A3
A4
& so on
AUTOFILL HORIZONTAL DATA
Sheet2
D3,D4,D5,D6..... fill by A1 A2 A3 A4.....
View 5 Replies
View Related
Mar 13, 2008
I was wondering is there a way to convert the format of one excel spreadsheet to a different format in another. I have attached a spreadsheet which illustrates what I'm trying to convert from and to. Perhaps VB Script? I have no idea how this could be resolved.
View 3 Replies
View Related
Jun 28, 2014
I am working with two spread sheets; one is my take-off/summary sheet which is where I populate all the data for the project I am working on, the second sheet is also a summary sheet but it has also other functions; hence, I need to have two spread sheets.
I need to link/reference the data I have on my take-off sheet row 119, column K through ACL (lots of columns) to my other sheet but it now needs to be referenced in a column direction. At first, I enter = (sign) in T9 (the sheet I need to bring the data in) and then I go to the take-off sheet select N119 and hit enter, this first data is good, but then I try to drag it down to populate all the data from the other sheet but even though I lock the row (N$119) and let N free the formula doesn't populate this: =!N$119, =!O$119, ='!P$119 and so on so for...all what it gives me is =!N$119 all the way down...
View 13 Replies
View Related
Oct 28, 2011
Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.
But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.
Is there any VBA function or public function to perform the this action?
Example:
From
A 1Dog; Lion; Parrot; Bee; Snail
To
A 7Dog8Lion9Parrot10Bee
11Snail
Like wise i will have to do the same action for the following
A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7
View 5 Replies
View Related
May 20, 2008
How do I move data that is in a vertical column to line up horizontally (in separate columns, but same row)? For example, take an address:
Name
Address
City/St
Zip
How do I get it to go from that format to this format?
Name Address City/St Zip
View 9 Replies
View Related
Apr 11, 2009
I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....
View 9 Replies
View Related
Mar 21, 2007
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.
I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
View 9 Replies
View Related
Jul 20, 2014
I have a large Item sheet that also functions as an order sheet so I want another sheet to return only items actually ordered. What is the correct vlookup formula for this situation?
in sheet #1, Column A has the number of orders needed (to be filled in), column B has the name of the product, column C has the price of the product. I want to look through column A after it is filled in and return the value of all three columns only if column A has a value larger than 0 into sheet #2 in the same workbook.
View 12 Replies
View Related
