Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two workbooks, say: workbook_a.xlsx and workbook_b.xlsx and both contain different worksheets. Two of those worksheets are already linked by a couple of VLOOKUP formulas using a unique key which is the custid column. With this setup I can let users enter data in the workbook_a and then I can run all my BI on the workbook_b. Now, one of the most tedious things I do is search for new high risk customers in workbook_a so I can manually add them to workbook_b. I would like to do the following:

  1. From workbook_a lookup custid in workbook_b.
  2. If it exists, do nothing (I already added the customer and the VLOOKUP formula will take care of updating the cust_risk cell value).
  3. If it doesn't exist, copy the row from workbook_a to workbook_b (maybe even not the whole row but just one cell?).

Is this possible? I'm using Excel 2007.

share|improve this question
    
Yes, you can do it in a VBA macro, I was whipping one up, but have hit some Excel bug and burned what time I had. I'll check back later when I have time to see if you still need an answer. –  Lance Roberts Jul 12 '11 at 16:15
    
Thanks @Lance, I will probably need an answer since I have no idea how to do it. I was actually looking to do it with formulas, to make the workbook more "portable". –  rdrgrtz Jul 12 '11 at 17:51
    
@rdrgtz, Formulas are no more portable than VBA, they are both 'in' the workbook. –  Lance Roberts Jul 13 '11 at 14:46
    
Thanks @lance, I wasn't aware that VBA resides "in" the workbook. –  rdrgrtz Jul 13 '11 at 18:47
    
Ok, I'm ready to work on this but don't know which cell you want to copy and where you want it to end up. Throw me the relevant column numbers and names on each sheet and I'll whip something up (I hope). –  Lance Roberts Jul 13 '11 at 20:33
show 1 more comment

2 Answers

up vote 0 down vote accepted

Yes, you can make a VBA macro for this.

I'd create a button, and in the click event, first make sure workbook_b is open, then use the following code to copy the rows where custid is in workbook_a, but not in workbook_b (note that in the end you just end up with a copy workbook_a in workbook_b, so it'd be a lot simpler to just copy the whole data range over).

Dim i As Integer, workingCol1 As Integer, workingCol2 As Integer
Dim workingRange1 As Range, workingRange2 As Range

workingCol1 = WorksheetFunction.Match("custid", Sheets("Sheet1").UsedRange.Rows(1), 0)
Set workingRange1 = Sheets("Sheet1").Range("AllCustomers").Columns(workingCol1)

workingCol2 = WorksheetFunction.Match("custid", Sheets("Sheet2").UsedRange.Rows(1), 0)
Set workingRange2 = Sheets("Sheet2").Range("CriticalCustomers").Columns(workingCol2)

For i = 2 To workingRange1.Rows.Count
   If Not IsError(Application.Match(workingRange1.Cells(i, 1), workingRange2, 0)) Then
    workingRange1.Rows(i).EntireRow.Copy
    Sheets("Sheet2").UsedRange.Rows(Sheets("Sheet2").UsedRange.Rows.Count).Offset(1, 0).EntireRow.PasteSpecial (xlPasteValues)
  End If
Next i

If you really only need one cell copied over, then you could do it with a worksheet-function, but it'd be a little complicated.

share|improve this answer
    
Nifty script @lance, but like you said, if the result is a workbook copy, then not much use. Also, the fact that it's VBA limits the use to one machine, and I want the function to work no matter where I take the Excel file (unless I'm mistaken and VBA is embedded in the Excel file itself). –  rdrgrtz Jul 13 '11 at 13:05
    
@rdrgrtz,I don't think you get what I'm saying. What you specified was what wrote, if you don't want it to be just a copy then specify something different. The VBA is in the file, just like any worksheet-function is in the file, so therefore I assume now you only want a menu-driven solution. Please verify and clarify your question if you really didn't want to end up with a copy. –  Lance Roberts Jul 13 '11 at 14:46
    
Thanks for the clarify @lance, I did get what you were saying, I just wasn't aware that VBA resided "in" the file. Your solution, as per required, is correct; I guess I just want a conditional copy of just one cell, and not the entire workbook. I'll mark this as answered, sorry for the upset. –  rdrgrtz Jul 13 '11 at 18:46
    
@rdrgrtz, I'll get back to you with a one-cell copy. –  Lance Roberts Jul 13 '11 at 19:06
add comment

If you're interested, another approach would be to first set up a dynamic named range in workbook_a, which covers the area where you permit users to enter data. Then, use that dynamic named range as the source for a pivot table in workbook_b.

Any changes you make to your data in workbook_a, including new customers, get picked up when you refresh the pivot table in workbook_b.

Note that both workbooks need to be open.

Specifically, when I played around with this, I named my dynamic range in workbook_a as "AllCustomerData", with the data starting in cell A1 on sheet1, and defined the formula for "AllCustomerData" as

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Next, for the pivot table source in workbook_b, I used

 workbook_a.xlsx!AllCustomerData

Then, when I built the pivot table, I chose the "Classic Pivot Table layout". I placed all my fields in the "RowLabels" section of the Pivot table Fields List box, in the order of my fields in the original workbook_a. You can, of course, order them as you desire -- or only select the field(s) you need.

When I tested this, I did see that if I deleted a customer, refreshed the pivot table, then re-used that deleted customer's CUSTID at the bottom of the source range, and again refreshed the pivot table, that the re-used CUSTID would show up in the pivot table not at the bottom, but in the original position before deletion. (I hope that was clear!) I don't know if your business rules permit re-use of CUSTIDs, but be aware of that if you do.

Hope this helps. The VBA approach of @Lance Roberts is real nice too. It has an advantage over this pivot table approach of not incurring all that "pivot table overhead".

share|improve this answer
    
Thanks for the answer @f106dart, but not what I'm looking for. –  rdrgrtz Jul 13 '11 at 13:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.