1

Here's my dilemma:

I have 2 excel sheets. 

- One is a target sheet with already existing information
- One is a source sheet

I would need to match the used cells in sourcesheet column C to target sheet column A used cells, and copy 3 cells per row from source to target(to adjacent rows).

So, let's say I have "TESTUSER test" in one of the cells in sourcesheet column C, and the same name somewhere in a cell in target sheet column A. From the row where the name is in the sourcesheet, I would need to copy the A,B,C column cells(only from that row) data to the targetsheet's row where the name is in column A, to columns C,D,E. This same operation would need to be done for each name in the sourcesheet column C.

I have managed to get the needed values from both sheets with "For each" methods, but I have no idea how to match the data and make the copying based on that.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kristian Helenius
  • 13
  • 6

2 Answers2

0

The 'correct' way would probably involve ADO/SQL, but as you have working For Each loops, you can use a dictionary to store the index/row numbers in Src under the name key and update Tgt if you find the name in that dictionary. In code:

  Const cnSKI = 2  ' Src Key Index
  Const cnTKI = 0  ' Tgt Key Index
  Const cnTUO = 2  ' Tgt Update Offset
  Const cnFTC = 2  ' Fields To Copy

  Dim aoaSrc : aoaSrc = Array( _
      Array("a", "b", "A1", "NotInTgt") _
    , Array("c", "d", "a1", "UpdInTgt") _
  )
  Dim aoaTgt : aoaTgt = Array( _
      Array("B1", "NotInSrc", "x", "x", "xx") _
    , Array("a1", "UpdInTgt", "?", "?", "??") _
  )
  Dim dicSrc : Set dicSrc = CreateObject("Scripting.Dictionary")
  Dim i
  For i = 0 To UBound(aoaSrc)
      dicSrc(aoaSrc(i)(cnSKI)) = i
  Next
  dumpAOA "Src", aoaSrc
  dumpDic "Src dic", dicSrc
  dumpAOA "Tgt", aoaTgt
  For i = 0 To UBound(aoaTgt)
      If dicSrc.Exists(aoaTgt(i)(cnTKI)) Then
         Dim r : r = dicSrc(aoaTgt(i)(cnTKI))
         Dim c
         For c = 0 To cnFTC
             aoaTgt(i)(c + cnTUO) = aoaSrc(r)(c)
         Next
      End If
  Next
  dumpAOA "Tgt (updated)", aoaTgt

Sub dumpAOA(sTitle, aoaX)
  WScript.Echo "----", sTitle
  Dim a
  For Each a In aoaX
      WScript.Echo "", Join(a)
  Next
End Sub

Sub dumpDic(sTitle, dicX)
  WScript.Echo "----", sTitle
  Dim k
  For Each k In dicX.Keys
      WScript.Echo "", k, dicX(k)
  Next
End Sub

output:

---- Src
 a b A1 NotInTgt
 c d a1 UpdInTgt
---- Src dic
 A1 0
 a1 1
---- Tgt
 B1 NotInSrc x x xx
 a1 UpdInTgt ? ? ??
---- Tgt (updated)
 B1 NotInSrc x x xx
 a1 UpdInTgt c d a1
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • Thanks! Say I have the values from both columns gathered and set as objects, where would I put them in the code? – Kristian Helenius Oct 16 '12 at 14:02
  • @KristianHelenius - I don't understand your question. Why objects? What code? How could you possibly put objects into code? – Ekkehard.Horner Oct 16 '12 at 14:13
  • I did it in bit different way, created a loop to gather values to an array from the source csv, and check if a entry in the array matches the value in the target sheet and then copy the contents of the array to the target to the right place. My problem now is that I cant compare the excel cell value to the array value, I get the error "type mismatch", does the excel cell value need to be reformatted/edited somehow before it can be used in comparison with an array value? – Kristian Helenius Oct 18 '12 at 13:24
  • @KristianHelenius - As you now face a completely different problem, post a new question focusing on comparison and types; be sure to show the pertinent code. (BTW: After mentioning dictionaries, I hesitate to become a partner in the crime of array-to-array compairing.) – Ekkehard.Horner Oct 18 '12 at 15:24
  • Ok, thanks for the answers, posted a new question: http://stackoverflow.com/questions/12969149/type-mismatch-compare-excel-cell-value-to-array-value – Kristian Helenius Oct 19 '12 at 07:15
0

Would using the Excel formula be easier? Assuming your source sheet is named "Sheet1", paste this formula in your target sheet's column C:

=IF(ISERROR(MATCH("TESTUSER test",Sheet1!C:C, 0)), "No Match", INDIRECT(ADDRESS(MATCH("TESTUSER test",Sheet1!C:C, 0), 1,,, "Sheet1")))

That gives you the value from Sheet1 column A (see the "1" somewhere in the formula). Then copy this into your target sheet's column D:

=IF(ISERROR(MATCH("TESTUSER test",Sheet1!C:C, 0)), "No Match", INDIRECT(ADDRESS(MATCH("TESTUSER test",Sheet1!C:C, 0), 2,,, "Sheet1")))

and this into column E:

=IF(ISERROR(MATCH("TESTUSER test",Sheet1!C:C, 0)), "No Match", INDIRECT(ADDRESS(MATCH("TESTUSER test",Sheet1!C:C, 0), 3,,, "Sheet1")))

The only difference in the formulas are the "1", "2" and "3", corresponding to columns A, B and C (in Sheet1 in this case).

(the answer is surprisingly similar to that of this question: Excel: Check if Cell value exists in Column, and then get the value of the NEXT Cell)

Community
  • 1
  • 1
Vincent Tan
  • 3,058
  • 22
  • 21
  • I suggest you use VLOOKUP instead. – CustomX Oct 16 '12 at 13:51
  • Hi, thanks for the answer, the thing is that the values vary in the columns, as the target sheet is produced by the same script from incoming data. So I can't define a specific name in the code. I have to take arrays from aforementioned columns of both sheets, match the values and copy data based on that. – Kristian Helenius Oct 16 '12 at 13:59
  • As thielemans mentioned, use VLOOKUP. You can use a cell reference instead of a fixed value such as "TESTUSER test". I assume that even though the cell values vary according to the target sheet, the cell references don't change, right? – Vincent Tan Oct 18 '12 at 08:15