Need help creating VBA to pull data from two worksheets into one worksheet

D

DiamondNate

New Member
Joined
Oct 13, 2014
Messages
4
First and foremost thank you for your help.

I have two forms of data, tabs 'SF Notes Added' and 'SFID-CID', that I need to combine into one worksheet, tab 'Notes to be Uploaded', for my records.

The first header in the 'Notes to be Uploaded' tab is Company ID. This would be a vlookup of the value in A4 'SF Notes Added' against 'SFID-CID'.

The second header 'Written By' is always 'nross'

The third header 'Written Date' will be the date in 'SF Notes Added' associated with the ID.

The forth header 'written note' is going to be a combination of cells based on the merged cells in 'SF Notes Added' (column d - column e: column F values separated by; for each row involved in the merged cells from column D)

Here's an example of the final output I'm looking for

company_id written_by written_date written_note
31117710/7/2014Services - Canelled: Sponsorhips Only - removed: Sponsorhips Only - removed; Maintenance/Service Providers (1); Professional Services (1); Independent Installers (1)

<tbody>
</tbody>

Here is 'SF Notes Added' tab
Account IDDateCompany / AccountActivity TypeSubjectFull Comments
Assigned: NR (14 records)
001i000000N9cua10/7/2014MediaCentric Integration Inc.ServicesCancelledSponsorhips Only - removed:
Maintenance/Service Providers (1)
Professional Services (1)
Independent Installers (1)
001i000000N9cvH10/7/2014Immedia Integrated TechnologiesTreatmentRedesignMatch new look and feel of company site.
001i000000N9cvH10/7/2014Immedia Integrated TechnologiesTreatmentLogoUploaded new company Logo
001i000000pS3ok10/8/2014NewTek, Inc.ProductsNewTriCaster Mini HD-4i
TriCaster Mini HD-4
TriCaster Mini Control Surface
001i000000pS3mf10/10/2014Atlona TechnologiesMedia CenterVideo(s)AT-UHD-CLSO-612 Basic IO Front Panel
AT-HD-M2C features
001i000000pS3nz10/10/2014Imagine CommunicationsMedia CenterVideo(s)WEBINAR: Future proofing Playout Operations
IBC2014: Redefining the Industry with Playout in the Cloud
LandmarkOSI™ Traffic & Billing | Same Day Program Changes
LandmarkOSI™ Traffic & Billing | Late Ads Made Easy
LandmarkOSI™ Traffic & Billing | Reducing Make Goods
LandmarkOSI™ Traffic & Billing | Making Quick Copy Changes
IBC2014 Highlights: Relive the excitement!
ImagineLIVE! Europe: Eight visionaries. One energy-packed event.
IBC2014 Keynote: Assessing the Health of Broadcast
001i000000pS3mX10/10/2014AMXMedia CenterVideo(s)TUTORIAL: How to Use Screen Mirroring with Enzo
On Screen! Display Content Wirelessly from Your Device in Seconds
AMX Campus Explorer Education App Delivers Interactive AV Planning to Assist Technology Managers
001i000000pS3pI10/10/2014TeamBoardMedia CenterVideo(s)TeamBoard IFP Overview
001i000000pS3nB10/10/2014ChiefMedia CenterVideo(s)Canada Road Show Tour
001i000000pS3nG10/10/2014Contemporary ResearchMedia CenterPress Release(s)Doug’s Q-Tips – What sources will work with QMOD’s HDMI input?
001i000000N9d3r10/10/2014Emcore CorporationTreatmentContact UsSecond time I'm having Dev remove their contact us. Still appearing on product detail pages.
001i000000pS3p010/10/2014Ricoh CorporationMedia CenterPress Release(s)New Ricoh Meeting Room Services Help Companies Improve The Way They Manage Their Workspace
001i000000pS3pd10/10/2014Williams SoundMedia CenterPress Release(s)Williams Sound sponsors Veterans Scholarship Program
001i000000N9cae10/10/2014Theatrical Services & Supplies, Inc.ServicesCancelledRemove AV Service Ad
Design/Build Contractors (1)
Grand Totals (14 records)

<tbody>
</tbody>


Here is 'SFID-CID' tab
Account IDCompany ID
001i000000cMfIb313170
001i000000ePzjl313030
001i000000ePzkz204700
001i000000ePzl5203875
001i000000ePzl8306535
001i000000ePzlC313098
001i000000ePzlO310949
001i000000ePzlX205592
001i000000ePzm4204715
001i000000eSNw3313305
001i000000hkAtj313310
001i000000N9ca0202927
001i000000N9caa308745
001i000000N9caB206353
001i000000N9caC205488
001i000000N9caD-
001i000000N9caE205229
001i000000N9cae200719
001i000000N9caG203939
001i000000N9caJ205796
001i000000N9caK307107
001i000000N9caO205918
001i000000N9cas203698
001i000000N9cau310243
001i000000N9caX301027
001i000000N9cb2204996
001i000000N9cbF206141
001i000000N9cbl312914
001i000000N9cc6203659
001i000000N9cc7312664
001i000000N9cci207596
001i000000N9ccz205319
001i000000N9cd0301660
001i000000N9cd1301791
001i000000N9cdb304618
001i000000N9cde200215
001i000000N9cdV204700
001i000000N9cdX202897
001i000000N9cef301003
001i000000N9ceh200033
001i000000N9cer203193
001i000000N9ceu309166
001i000000N9cev301054
001i000000N9cfE205281
001i000000N9cfG203804
001i000000N9cgE308112
001i000000N9cgF205195
001i000000N9cgG300698
001i000000N9cgH301539
001i000000N9cgZ204725
001i000000N9chj206016
001i000000N9cho302451
001i000000N9chU301182
001i000000N9ciG311867
001i000000N9ciI206017
001i000000N9ciS203693
001i000000N9cja-
001i000000N9ckN310328
001i000000N9ckn311455
001i000000N9cko204786
001i000000N9ckr300549
001i000000N9ckx308942
001i000000N9cky307323
001i000000N9clr312266
001i000000N9cmP-
001i000000N9cmQ310227
001i000000N9cn5203791
001i000000N9cnR301080
001i000000N9cnS310296
001i000000N9coE302979
001i000000N9coF202914
001i000000N9coG204855
001i000000N9coH204855
001i000000N9coI311023
001i000000N9coi203002
001i000000N9coJ204875
001i000000N9coL204958
001i000000N9coN204654
001i000000N9coO204986
001i000000N9coo-
001i000000N9coP202896
001i000000N9cop301737
001i000000N9coR301572
001i000000N9cpf203780
001i000000N9cph311919
001i000000N9cpP311232
001i000000N9cpX300017
001i000000N9cpY203834
001i000000N9cq1200315
001i000000N9cq5311034
001i000000N9cqA200969
001i000000N9cqe307015
001i000000N9cqH202985
001i000000N9cQk200748
001i000000N9cQl301368
001i000000N9cQm202990
001i000000N9cQo307075
001i000000N9cQp302429
001i000000N9cQs310686
001i000000N9cr7306955
001i000000N9crJ203770
001i000000N9csc311333
001i000000N9cse300045
001i000000N9csi207449
001i000000N9csk301100
001i000000N9csP300061
001i000000N9css306264
001i000000N9cst311135
001i000000N9csX206051
001i000000N9ct1310649
001i000000N9ct2300734
001i000000N9ct3202977
001i000000N9ct4302995
001i000000N9ct8204791
001i000000N9ctA311519
001i000000N9cTc203707
001i000000N9cTF-
001i000000N9cTQ-
001i000000N9cTw-
001i000000N9ctx206014
001i000000N9cua311177
001i000000N9cUb-
001i000000N9cub300921
001i000000N9cuc306187
001i000000N9cud203852
001i000000N9cue310667
001i000000N9cuj302978
001i000000N9cUo-
001i000000N9cUS301385
001i000000N9cUw-
001i000000N9cuW207612
001i000000N9cuw307642
001i000000N9cuX205732
001i000000N9cuy309860
001i000000N9cv0307834
001i000000N9cv7306873
001i000000N9cv9307873
001i000000N9cvA202996
001i000000N9cvB310979
001i000000N9cvD202916
001i000000N9cvH306181
001i000000N9cvK205681
001i000000N9cvQ205718
001i000000N9cvR205723
001i000000N9cVv300499
001i000000N9cvY202920
001i000000N9cW5203402
001i000000N9cw5306663
001i000000N9cwb206898
001i000000N9cwE307335
001i000000N9cwT311479
001i000000N9cwV206067
001i000000N9cwX206162
001i000000N9cx3306675
001i000000N9cxd207000
001i000000N9cXe206494
001i000000N9cxe311241
001i000000N9cXR302913
001i000000N9cXs309843
001i000000N9cXu309094
001i000000N9cXv306024
001i000000N9cXw307571
001i000000N9cxW310466
001i000000N9cXx306365
001i000000N9cxX200229
001i000000N9cxZ311780
001i000000N9cY2302669
001i000000N9cY3202993
001i000000N9cY4308111
001i000000N9cY5200104
001i000000N9cYA301048
001i000000N9cYB205335
001i000000N9cYC204666
001i000000N9cYD301524
001i000000N9cYE202980
001i000000N9cYF309845
001i000000N9cYG203854
001i000000N9cyh311866
001i000000N9cYN203128
001i000000N9cYp206497
001i000000N9cYr203667
001i000000N9cYv308477
001i000000N9cYW204757
001i000000N9cYX301796
001i000000N9cYY301745
001i000000N9cYZ311552
001i000000N9cZ0-
001i000000N9cz0306243
001i000000N9cz1301004
001i000000N9cZ8306185
001i000000N9cZ9309304
001i000000N9cZA306534
001i000000N9cZE203951
001i000000N9cZJ308393
001i000000N9cZR204936
001i000000N9cZu307943
001i000000N9cZV308517
001i000000N9cZx306509
001i000000N9cZz300552
001i000000N9d3q308508
001i000000N9d3r311884
001i000000N9d42306667
001i000000N9d58-
001i000000N9d5a203206
001i000000OVbrA312667
001i000000pS3mb210018
001i000000pS3mf305815
001i000000pS3mk203225
001i000000pS3mL301200
001i000000pS3mM310971
001i000000pS3mN301808
001i000000pS3ms303217
001i000000pS3mT203066
001i000000pS3mu207332
001i000000pS3mW203054
001i000000pS3mw300085
001i000000pS3mX206548
001i000000pS3my301652
001i000000pS3n0203465
001i000000pS3n2203146
001i000000pS3n3301676
001i000000pS3n6204786
001i000000pS3n8305830
001i000000pS3nA301379
001i000000pS3nB203122
001i000000pS3nc203331
001i000000pS3nD203255
001i000000pS3ne302851
001i000000pS3nG203252
001i000000pS3nH203182
001i000000pS3ni301734
001i000000pS3nJ308533
001i000000pS3nj302025
001i000000pS3nK309207
001i000000pS3nl306601
001i000000pS3nM302686
001i000000pS3nO203198
001i000000pS3no203065
001i000000pS3nQ203451
001i000000pS3nq203191
001i000000pS3nr302520
001i000000pS3nu305787
001i000000pS3nV203156
001i000000pS3nW203330
001i000000pS3nw203115
001i000000pS3nZ301258
001i000000pS3nz302109
001i000000pS3o4203174
001i000000pS3o7300145
001i000000pS3oA203126
001i000000pS3oa203365
001i000000pS3ob300513
001i000000pS3oC305792
001i000000pS3oe203369
001i000000pS3oF301566
001i000000pS3oH301192
001i000000pS3oJ307591
001i000000pS3ok203623
001i000000pS3oL300083
001i000000pS3oN311901
001i000000pS3oo306672
001i000000pS3oP312024
001i000000pS3op203079
001i000000pS3or203107
001i000000pS3oT302108
001i000000pS3ot302726
001i000000pS3oV308219
001i000000pS3ov301323
001i000000pS3oW307291
001i000000pS3oX308931
001i000000pS3oy203142
001i000000pS3p0206580
001i000000pS3p2302649
001i000000pS3p6300698
001i000000pS3p8203183
001i000000pS3pB203120
001i000000pS3pb203423
001i000000pS3pD305409-EU
001i000000pS3pd203428
001i000000pS3pE304566
001i000000pS3pf203209
001i000000pS3pG305989
001i000000pS3ph306929
001i000000pS3pI203329
001i000000pS3pj312667
001i000000pS3pk310264
001i000000pS3pL307530
001i000000pS3pM203450
001i000000pS3pm-
001i000000pS3pO305868
001i000000pS3pQ301261
001i000000pS3pq-
001i000000pS3pS300113
001i000000pS3pV203718
001i000000pS3pX203121
001i000000pS3px-
001i000000pS3pZ203059
001i000000pS3q4-
001i000000pS3q9-
001i000000pS3qB-
001i000000pS3qD-
001i000000pS3qf-
001i000000pS3qg-
001i000000pS3qI-
001i000000pS3qi-
001i000000pS3qn-
001i000000pS3qQ-
001i000000pS3qr-
001i000000pS3qS-
001i000000pS3qT-
001i000000pS3qV-
001i000000pS3qY-
001i000000pS3qZ-
001i000000pS3r6-
001i000000pS3rA-
001i000000pS3rb-
001i000000pS3rc-
001i000000pS3rD-
001i000000pS3rF-
001i000000pS3rG-
001i000000pS3rh-
001i000000pS3ri-
001i000000pS3rM-
001i000000pS3rn-
001i000000pS3ro-
001i000000pS3rS-
001i000000pS3rs-
001i000000pS3rt-
001i000000pS3rw-
001i000000pS3rx-
001i000000pS3s1-
001i000000pS3s3-
001i000000pS3s4-
001i000000pS3s5-
001i000000pS3sF-
001i000000pS3sG-
001i000000pS3sN-
001i000000pSjOb305409
001i000000sVXZd310328
001i000000sVXZe-
001i000000X8rQe312954
001i000000zG8dT315427
001i0000019eth0-

<tbody>
</tbody>


Thanks for your help!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
I am assuming that the data shown between Account IDs is a glitch in the html copy over and that it actually is part of the 'Full Comments' column. If it is part of column A then this code will fail.
Code: 
Sub combineDiamondNate()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, c As Range, fn As Range
Set sh1 = Sheets("SF Notes Added") 'Validate sheet name
Set sh2 = Sheets("SFID-CID") 'Validate sheet name
Set sh3 = Sheets(3) 'Edit sheet name-this is the combined sheet which you must designate.
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                sh3.Cells(Rows.Count, 1).End(xlUp)(2) = fn.Offset(0, 1).Value
                sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "nross'"
                sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = c.Offset(0, 1).Value
                sh3.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3) = c.Offset(0, 5).Value
            End If
    Next
End Sub
 
Upvote 0
Hi JLGWhiz

Thank you so much for your help.

The VBA you provided, when tested, had a view issues.

1) The Account ID's are case-sensitive so you could have 11upD and 11UpD.
2) The Date wasn't being pulled in on some.
3) The note field is note quiet working properly. Should be coulmn d - column e: colum F row(); column F row(); for all rows columns a:e have merged.

Here is the link to view a sample of the excel file
https://drive.google.com/file/d/0B0dzgXh022zddENXNGZjUmhYVW8/view?usp=sharing
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
A
  • Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,215,219
Messages
6,123,689
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top