Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
I am looking for a help with vlookup.
I have the following table. ID code Trans_Dat Debit 02/01/10 02/02/10 02/03/10 630153 1000 2/5/10 $103.00 $- $- $- 630153 1000 2/6/10 $103.00 $- $- $- 630155 1000 2/1/10 $107.20 $107.20 $- $- 630155 1000 2/2/10 $107.20 $- $107.20 $- 630155 1000 2/3/10 $107.20 $- $- $107.20 630155 1000 2/4/10 $107.20 $- $- $- 630155 1000 2/5/10 $107.20 $- $- $- 630155 1000 2/6/10 $99.20 $- $- $- 630156 1000 2/5/10 $88.18 $- $- $- 630156 1000 2/6/10 $88.18 $- $- $- 630157 1000 2/5/10 $68.84 $- $- $- 630157 1000 2/6/10 $68.84 $- $- $- 630158 1000 2/6/10 $68.53 $- $- $- 630159 1000 2/3/10 $60.98 $- $- $60.98 630159 1000 2/4/10 $60.98 $- $- $- 630159 1000 2/5/10 $60.98 $- $- $- As you can see the ID number repeats itself. What I am trying to do is rearrange data so that all cost data per ID is on one line by date. This ID is equal an employees name which is on sheet 1. Below is an example of what I am trying to achieve using the chart above: 2/1/10 2/2/10 2/3/10 Jones ID-630155 107.20 107.20 107.20 I looked at HLOOKUP, MATCH and INDEX and I can't get the results I need. The first chart encompasses about 45 date columns and about 25000 rows of data. Thanks for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
If you can accept a VBA solution, it is posted below. It assumes that the
sheets are laid out as you've indicated starting with Column A on both sheets. Also assumes that the names/IDs on 'Sheet1' are all set up as you indicated in the example: it looks for the dash to figure out where the ID number starts. To put the code into (a copy of) your workbook for testing, open the copy and press [Alt]+[F11] to get into the VB Editor then choose Insert -- Module. Copy the code below and paste it into the module and make any changes needed such as to the worksheet names. Close the VB Editor. You can now run the macro from Tools -- Macros -- Macro, or put a button (from the Forms toolbar) or shape on the sheet and assign that macro to it for ease of use. Here's the code: Sub ConsolidateList() 'these refer to the sheet with just 'names & ID (as Jones ID-630155) on it Dim nameWS As Worksheet Dim namesList As Range Dim anyName As Range Dim nameID As Long ' will hold just '630155' 'little hard to explain, but we want this number, 'when subtracted from firstDateCol (below) to 'equal 1 Const nameOffsetAdjust = 3 'these refer to the sheet with the large 'list of IDs and dates Dim listWS As Worksheet Dim bigList As Range Dim anyListEntry As Range Const firstDateCol = 4 ' column E=5, -1 to use as offset = 4 Dim lastDateCol As Integer 'miscellaneous values Dim CLC As Integer ' will be column loop counter Dim tempRange As Range Dim lastNameRow As Long Dim lastNameCol As Long 'change the sheet name as required Set nameWS = ThisWorkbook.Worksheets("Sheet1") Set namesList = nameWS.Range("A2:" & _ nameWS.Range("A" & Rows.Count).End(xlUp).Address) 'change the sheet name as required Set listWS = ThisWorkbook.Worksheets("BigList") Set bigList = listWS.Range("A2:" & _ listWS.Range("A" & Rows.Count).End(xlUp).Address) 'start by copying all dates from the big list sheet 'onto the names list sheet at B1 Set tempRange = listWS.Range("E1:" & _ listWS.Range("E1").End(xlToRight).Address) tempRange.Copy nameWS.Range("B1") 'zero out any existing entries in the area for $ amounts 'on the names sheet (Sheet1) lastNameRow = nameWS.Range("A" & Rows.Count). _ End(xlUp).Row lastNameCol = nameWS.Range("B1").End(xlToRight).Column Set tempRange = nameWS.Range("B2:" & _ Cells(lastNameRow, lastNameCol).Address) 'set to "Accounting" number format tempRange.NumberFormat = _ "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" 'and zero out any existing values tempRange.Value = 0 'we want this for checking out entries in the date columns 'we subtract 1 so it can be used as an offset later lastDateCol = listWS.Range("E1").End(xlToRight).Column - 1 'now we start the big task of matching things up 'this assumes that the entries on Sheet1 are as you 'indicated: ID number follows a dash (-) character 'I did it this way rather than faster method of 'simply grabbing the last 6 characters to use as 'the ID number. For Each anyName In namesList 'do not process cells that are empty or 'that do not have a "-" in them If Not IsEmpty(anyName) And _ InStr(anyName, "-") 0 Then 'get the ID number - treat it as a number nameID = Val(Right(anyName, Len(anyName) - _ InStr(anyName, "-"))) 'find entries in the big list that match 'this nameID For Each anyListEntry In bigList If anyListEntry = nameID Then 'have an ID match, find non-zero entries For CLC = firstDateCol To lastDateCol If anyListEntry.Offset(0, CLC) 0 Then anyName.Offset(0, CLC - nameOffsetAdjust) = _ anyListEntry.Offset(0, CLC) End If ' test for non-zero $ amount Next ' end CLC loop through dates End If ' end ID match test Next ' end anyListEntry loop End If ' end test for empty cell Next ' end anyName loop 'good housekeeping and cleanup Set tempRange = Nothing Set namesList = Nothing Set nameWS = Nothing Set bigList = Nothing Set listWS = Nothing 'and let the user know the job is done MsgBox "Lists have been consolidated.", vbOKOnly, "Task Complete" End Sub "Stenbeck" wrote: I am looking for a help with vlookup. I have the following table. ID code Trans_Dat Debit 02/01/10 02/02/10 02/03/10 630153 1000 2/5/10 $103.00 $- $- $- 630153 1000 2/6/10 $103.00 $- $- $- 630155 1000 2/1/10 $107.20 $107.20 $- $- 630155 1000 2/2/10 $107.20 $- $107.20 $- 630155 1000 2/3/10 $107.20 $- $- $107.20 630155 1000 2/4/10 $107.20 $- $- $- 630155 1000 2/5/10 $107.20 $- $- $- 630155 1000 2/6/10 $99.20 $- $- $- 630156 1000 2/5/10 $88.18 $- $- $- 630156 1000 2/6/10 $88.18 $- $- $- 630157 1000 2/5/10 $68.84 $- $- $- 630157 1000 2/6/10 $68.84 $- $- $- 630158 1000 2/6/10 $68.53 $- $- $- 630159 1000 2/3/10 $60.98 $- $- $60.98 630159 1000 2/4/10 $60.98 $- $- $- 630159 1000 2/5/10 $60.98 $- $- $- As you can see the ID number repeats itself. What I am trying to do is rearrange data so that all cost data per ID is on one line by date. This ID is equal an employees name which is on sheet 1. Below is an example of what I am trying to achieve using the chart above: 2/1/10 2/2/10 2/3/10 Jones ID-630155 107.20 107.20 107.20 I looked at HLOOKUP, MATCH and INDEX and I can't get the results I need. The first chart encompasses about 45 date columns and about 25000 rows of data. Thanks for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
Excel 2007 PivotTable
Running daily balance. No code, no formulas. http://www.mediafire.com/file/dgcnomzjwme/02_28_10.xlsx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
I have the following table.
ID * * *code * *Trans_Dat * * * *Debit *02/01/10 * * * *02/02/10 * * * *02/03/10 630153 *1000 * *2/5/10 * $103.00 * * * * $- * * *$- * * *$- * 630153 *1000 * *2/6/10 * $103.00 * * * * $- * * *$- * * *$- * 630155 *1000 * *2/1/10 * $107.20 * * * * $107.20 * * * * $- * * *$- * 630155 *1000 * *2/2/10 * $107.20 * * * * $- * * *$107.20 * * * * $- * 630155 *1000 * *2/3/10 * $107.20 * * * * $- * * *$- * * *$107.20 630155 *1000 * *2/4/10 * $107.20 * * * * $- * * *$- * * *$- * 630155 *1000 * *2/5/10 * $107.20 * * * * $- * * *$- * * *$- * 630155 *1000 * *2/6/10 * $99.20 * * * * *$- * * *$- * * *$- * 630156 *1000 * *2/5/10 * $88.18 * * * * *$- * * *$- * * *$- * 630156 *1000 * *2/6/10 * $88.18 * * * * *$- * * *$- * * *$- * 630157 *1000 * *2/5/10 * $68.84 * * * * *$- * * *$- * * *$- * 630157 *1000 * *2/6/10 * $68.84 * * * * *$- * * *$- * * *$- * 630158 *1000 * *2/6/10 * $68.53 * * * * *$- * * *$- * * *$- * 630159 *1000 * *2/3/10 * $60.98 * * * * *$- * * *$- * * *$60.98 630159 *1000 * *2/4/10 * $60.98 * * * * *$- * * *$- * * *$- * 630159 *1000 * *2/5/10 * $60.98 * * * * *$- * * *$- * * *$- * As you can see the ID number repeats itself. *What I am trying to do is rearrange data so that all cost data per ID is on one line by date. *This ID is equal an employees name which is on sheet 1. Below is an example of what I am trying to achieve using the chart above: * * * * * * * * * * * * * * * *2/1/10 * * 2/2/10 * *2/3/10 Jones ID-630155 * * * 107.20 * * 107.20 * 107.20 Here's one approach, but it doesn't use vlookup. In my example, the original data is in Sheet2 (starting with the first header in A1) and the result is in Sheet4. The dollar figures are formatted Excel numbers. There isn't much about Sheet1 in the post, so I'll leave it as an exercise how to pull in the employee names. I'm using Excel 2003. The following goes in Sheet4. In C1, D1, E1, put the dates. Column A is a "helper" column to identify one row for each ID. In A2, put =IF(COUNTIF(Sheet2!$A$2:Sheet2!$A2,Sheet2!A2)=1, MAX(A$1:A1)+1, "") It can be hidden later to avoid clutter. Column B is a list of unique IDs. In B2, put: =IF(ROW()-1MAX(A:A),"", OFFSET(Sheet2!$A$1,MATCH(ROW()-1,A:A,0)-1,0)) In C2 put =IF($B2="","", SUMPRODUCT(--(Sheet2!$A$2:$A$17=$B2), OFFSET(Sheet2!$A$2,0, MATCH(C$1,Sheet2!$1:$1,0)-1,16,1))) Copy rightward to E2. Select A2:E2 and copy down to row 17. Modify to suit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |