vlookup

[Stenbeck]

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

 

[JLatham]

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

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Running daily balance.
No code, no formulas.
http://www.mediafire.com/file/dgcnomzjwme/02_28_10.xlsx

 

[zvkmpw]

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()-1>MAX(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.