[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

2 excel sheets match 2 geather

Posted on 2007-07-30
8
Medium Priority
?
213 Views
Last Modified: 2010-03-05
Hi,

I have  2 excel  worksheets which has emp id's as common in both the files.
What i want is a 1st box asking me to search for in file A and file B colums.When the same emp id is found in file B then it should ask what all colums to copy to File A when i specify this it has to copy all colum text i specified to the end of each emp row.

Regards
Sharath
0
Comment
Question by:bsharath
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19598194
This macro can solve half the problem, i.e it wont sort the excel file as per txt fileI think that is tricky
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = False
      FF = FreeFile
      Open "C:\filename.txt" For Input As #FF

       While Not EOF(FF)
           Line Input #FF, str1
           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = True
            End If
        Wend
        Close FF
       If idfound = False Then
             Sheet1.Rows(i).Cut Destination:=Sheet2.Rows(s2row)
             Sheet1.Rows(i).Insert(xlDown)
            s2row = s2row + 1
       End If
 Next

End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19598909
It should ask what all colums to copy to File A
when i specify this it has to copy all colum text i specified to the end of each emp row.
Sort the same order as the file A
Can you add these points in the Macro please.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599057
?Sort the same order as the file A

i did not get this
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Author Comment

by:bsharath
ID: 19599077
If file A is as
Sharath
reddy
ramesh
And file B has
Ramesh
reddy
Sharath
Need to sort them a file A
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19599117
I have  2 excel  worksheets which has emp id's as common in both the files.
What i want is a 1st box asking me to search for in file A and file B colums.When the same emp id is found in file B then it should ask what all colums to copy to File A when i specify this it has to copy all colum text i specified to the end of each emp row.

example
file A
123  Marie
145 hit
156 Sharath

file B
145 M
156 M
123 F

Then file A should have following if column 2 is given to copy
123  Marie F
145 hit      M
156 Sharath M
right?
0
 
LVL 11

Author Comment

by:bsharath
ID: 19599238
File B may have
123  Marie  F
145 hit  F
156 Sharath M
So it has to ask me whichcolums to copy.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599412
say if you select column 2 then what will be the output?
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19608136
in the above case if i select column 2 then what should be the output
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question