Copying values in excel to another sheet, based on other values in the sheet

Question

Ok, the question sounds pretty vague, but i will try to explain.

I am trying to copy certain cell values from one sheet into another sheet. The place it should copy it to, is determined by another value in the same sheet. For example:

Sheet1

4040-5056 ----- 4040-5056v1.7

3409-5793 ----- 3409-5793v4.3

Sheet2

4040-5056

3409-5793

Based on the first values you see, the second column of values in sheet1 should be copied in the corresponding cells in sheet2.

I have no idea how to do this, any help would be appreciated!

Thanks in advance

EDIT:

Sheet1 contains all values that have to be copied to the corresponding nvalues in the other sheets. The values it has to correspond with are spread over 30 sheets or so, but all in the same document. In every sheet the values the code has to look for are all in the same column, so in every sheet is should look whether the value is the same in column A. VLOOKUP works, but is still a slow option, knowing that is handles over 36.000 rows. What the code should do, is copy the value of column B to one of the other sheets, if the value of column A corresponds with the value of column A in the other sheet.

I hope everyone can understand this explanation.

have you tried `VLOOKUP`? You should be able to use that if there is only one occurrence of your "ID numbers" in Sheet1 — Jaycal, Sep 05 '13 at 11:59
Please edit your question to make it more precise. Your question does not say it should be pure VBA ; nor that there are several sheets. You should specifically describe how the data is arranged over several sheets (are the range fixed ? dynamic ?) — d-stroyer, Sep 05 '13 at 12:25

score 0 · Answer 1 · answered Sep 05 '13 at 12:06

0

You can use the VLOOKUP function. No need to use VBA for this.

Sheet 1 :

       A          B         C
1      4040-5056  4040-5056v1.7
2      3409-5793  3409-5793V4.3
3

Sheet 2:

       A          B         C
1      4040-5056  =VLOOKUP(A1;Sheet1!$A$1:$B$2;2;FALSE)
2      3409-5793  =VLOOKUP(A2;Sheet1!$A$1:$B$2;2;FALSE)
3

Cell B1 will display "4040-5056v1.7", cell B2 "3409-5793V4.3"

Note that the last argument to VLOOKUP (here FALSE) is important in your case since the data is unsorted.

answered Sep 05 '13 at 12:06

d-stroyer

2,638
2
19
31

I have used that, as a test, unfortunately the data is spread over multiple sheets, so I am looking for code that will solve this for all sheets. Also, i am not the only one working in this sheet, for the other users it is very important it does not involve writing a formula or having to copy down stuff. Thanks for the answer though, but i am really looking vor a vba solution. – user2750501 Sep 05 '13 at 12:15

score 0 · Answer 2 · edited May 23 '17 at 12:28

Another solution, using pure VBA:

Since there is a strong need for performance, I suggest using a dict object, as hinted in this SO question. The advantage of using a dictionary is that it is very fast to lookup, once it is built. So I expect my code to be very quick in the lookup. On the other hand, it will be slower in accessing the individual cells (through loops) than the builtin VLOOKUP.

Comparative performance, using a reference sheet fof 30'000 entries, and 3 lookup sheets with 30'000 lines each:

VLOOKUP : 600 seconds
VBA / dictionary : 3 seconds

so the performance is 200x better with VBA dictionary in this context.

Note : you have to add a reference to "Microsoft Scripting Runtime" (from the tools->Reference menu of the VBA window)

Note : this solution will not work if the data in the reference sheet is not contiguous, or if there are duplicates.

Sub FillReferences()

  Dim dict As New Scripting.Dictionary
  Dim myRow As Range
  Dim mySheet As Worksheet

  Const RefSheetName As String = "sheet1"
  ' 1. Build a dictionnary
  Set mySheet = Worksheets(RefSheetName)
  For Each myRow In mySheet.Range(mySheet.Range("A1").End(xlDown), mySheet.Range("A" & mySheet.Rows.Count).End(xlUp))
    ' Append A : B to dictionnary
    dict.Add myRow.Value, myRow.Offset(0, 1).Value
  Next myRow

  ' 2. Use it over all sheets
  For Each mySheet In Worksheets
    If mySheet.Name <> RefSheetName Then
      ' Check all cells in col A
      For Each myRow In mySheet.Range(mySheet.Range("A1").End(xlDown), mySheet.Range("A" & mySheet.Rows.Count).End(xlUp))
        ' Value exists in ref sheet ?
        If dict.exists(myRow.Value) Then
          ' Put value in col B
          myRow.Offset(0, 1).Value = dict(myRow.Value)
         End If
      Next myRow
    End If
  Next mySheet

End Sub

Copying values in excel to another sheet, based on other values in the sheet

2 Answers2