Transfereing data

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Transfereing data

Post by jimpatel1993 »

Thanks for registering me in this forum. I got stuck with something and any help will be appreciated.
I have 2 excel one is source called Transfer data and other one is destination called Workbook. Some of the VBA code is already implemented with help of excel formum and i have transfered data. Now the issue is i have added sheet2 in the source work book with some data's having same unique ID and name from sheet 1 and some are new (highlighted in yellow for same unique id from sheet 1). It is exactly same concept but for some reason i am not getting luck in getting the solution. what i am after is I wanted to move data from sheet 2 (Sourceworkbook) to destination and if the same unique ID repeats in both sheet 1 and sheet 2 then i wanted to add in the respective column and row in the destination sheet. If it is new unique ID then i am looking for adding new additional row with same above concept. Any idea guys?
Thanks again
Jim
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

Welcome to Eileen's Lounge!

The code is rather complicated, I'll have to study it before I can get back to you.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

Thanks a lot for your reply. I dont mind if you could provide me some simple code.
Thanks a lot again

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

Or the logic is simple. Transferring data from one excel to another which is attached in the previous message. But the issue is i wanted to transfer to destination as it is shown please.
Thanks a lot for your help

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

I'm afraid that the code has been written in a way that I don't understand. Can you explain in words what it does?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

Sure. I have attached the word format of full explanation of what i am looking for. The code does some of them which is attached. Sorry for my poor explanation and let me know if you need more detail explanation :)

Thanks,
Jim
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

I'll take a look after dinner.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

Thanks a lot hans
Much appreciated
Thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

If you wish, I can modify the code to combine the data from Sheet1 and Sheet2 into the same columns (A to P) in Workbook2.
To get them into different columns is too complicated for me. If you really need that, you should ask maras_mak again - he wrote the code.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

Thanks for your reply hans. To be honest i need it in different column as i mentioned in the word document. I have requested maras but unfortunately there is no answer for more than 3 days and thats why i wanted to give a try here hans.
Thanks
Jim

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

Perhaps that's because it's the weekend. Let's hope that they come up with a solution eventually,
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfereing data

Post by jimpatel1993 »

True. Hopefully. I know this is big complicated but i hope i will get some solution.
Really thanks hans for your time and effort though.
Thanks again
Jim

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

Hi Jim
I took a quick look at this. I think maras_mak has done a lot of quite complicated coding for you.
( https://www.excelforum.com/excel-progra ... -data.html )
Its not necessarily difficult coding, but a lot of it. So it will be difficult for anyone else to catch up on whats been going on.

The coding is written in a coding form similar to how I like to do theses sort of things. So I think I could understand it.
Never the less, I think it would take me a very long time to slowly go through and understand every detail of maras_mak’s coding, at any rate it would take me a very long time to slowly go through and understand enough of it to be able to modify it.
In such a case I think the best person to modify it would be maras_mak or yourself once you have taken the time to understand it.
I don’t want to waste my time too soon looking at this in case maras_mak (or yourself once you have taken the time to understand it), can modify it easier. That would be a lot more efficient.
Often when you need “just a simple modification” it can mean that first you need to understand the original problem and solution which can take a long time.

( maras_mak may have just decided that enough is enough. Often regulars at a forum decide at a certain point just to move on. In a free forum, especially the larger ones like excelforum, that’s not uncommon and perfectly normal / acceptable. ( If you haven’t already done so you could give maras_mak a few rep points, some regulars like that sort of thing… Hit the * star button bottom left Add Reputation) ###)

But I will take another look in a few days, to see if you still need help**.

Probably in the meantime you should try to work through and understand the coding. I think its not a sensible thing to do to get a very complicated coding from a forum and then keep asking for a modification. Just my opinion.
I expect the modifications would not be too difficult, once you understand the coding so far.
But to do that would take a long time I think.

**So I will look in again on this ( here) in a couple of days. So please tell us all in the meantime if you get your solution.

Alan

### P.S. I only mention all that as you are a new poster.
The other thing to note is that forums often prefer you to mention when you post the same question in another forum. So you might want to edit your last post at excelforum to include a link to this Thread at Eileen’s Lounge ( http://www.eileenslounge.com/viewtopic.php?f=27&t=36063 )
If you have difficulty posting a link at excelforum because you are a new poster, then disguise the link something like
H t t p : / / w w w . eileenslounge.com/viewtopic.php?f=27&t=36063
Alternatively just tell them that you have “cross posted” the question at Eileen’s Lounge
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Transfereing data

Post by p45cal »

A different approach maybe, Power Query.
The attached contains only the beginning of a possible solution.
The SourceWorkbook is just that, the Eileen'sLounge36063PQTransfer data.xlsx workbook contains the query.
Use them together initially to check that it works.
Save the SourceWorkbook wherever, and it can remain closed.
Open the TransferData workbook, right-click on the table therein and choose Refresh. It will fail because the SourceWorkbook is not in the same place as on my machine.
So follow this once-only procedure to point to the SourceWorkbook:
On the Data tab of the ribbon, in the Queries & Connections section, click on the Queries & Connections icon. This will open a pane on the right. Choose the Queries at the top if it's not already selected. You should then see Table1 'Download did not complete'.
Right-click Table1 and choose Edit… This will open the Power Query editor.
On the right hand side you'll see an area Applied Steps, and below that a series of several steps. (You can click on these to see what's going on.)
Next, on the Home tab of the menu, in the Query section, click Refresh Preview. It will complain with a yellow band. Click on the Source step on the right and you'll see a button: Edit Settings: Click it. It will allow you to browse to the SourceWorkbook you saved earlier, then click OK.
The query should now work. (If you click on the last step, Changed Type, you should see the final table.)
All you need to do now is to Close & Load which is at the extreme left of the Editor's ribbon's Home tab. The data should refresh automatically, if not, right-click the table an choose Refresh as we did right at the start.

Now for your actual data, the query expects to see a table called Table1 in the source workbook - it doesn't matter what sheet it's on, and the table should contain at least the same exact headers as in the SourceWorkbook attached; it can contain more columns.
Of course you'll have to take that once-only step again with your real source file.

I don't know what you intend with the gap columns?

I'll read more fully the files you've attached earlier including the .docx file which I haven't looked at yet.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfereing data

Post by HansV »

I don't know if you're still following this, but since you didn't get a further reply in ExcelForum, here is a macro for Sheet2. It is much less clever than the one for Sheet1, and less efficient, but that code was too complicated for me to analyze.
Run the following macro after Transfer_maras.

Code: Select all

Sub Transfer_Sheet2()
    Const pth = "C:\Users\Desktop\Excel\"      '<---- use own path
    Const wnm = "Workbook2.xlsx"               'your workbook name
    Dim wrbk As Workbook
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim ID As Long
    Dim rng As Range
    Dim s As Long
    Dim c As Long

    Application.ScreenUpdating = False

    On Error Resume Next
    Set wrbk = Workbooks(wnm)
    If wrbk Is Nothing Then
        Set wrbk = Workbooks.Open(Filename:=pth & wnm)
    End If
    On Error GoTo 0
    Set w1 = wrbk.Worksheets("Sheet1")
    Set w2 = ThisWorkbook.Worksheets("Sheet2")
    m = w2.Range("B" & w2.Rows.Count).End(xlUp).Row
    For r = 2 To m
        ID = w2.Range("B" & r).Value
        Set rng = w1.Range("B:B").Find(What:=ID, LookAt:=xlWhole)
        If rng Is Nothing Then
            s = w1.Range("B" & w1.Rows.Count).End(xlUp).Row + 1
            w1.Range("B" & s).Value = ID
            w1.Range("D" & s).Value = w2.Range("C" & r).Value
            w1.Range("E" & s).Value = w2.Range("D" & r).Value
            w1.Range("F" & s).Value = w2.Range("E" & r).Value
            w1.Range("G" & s).Value = w2.Range("K" & r).Value
        Else
            s = rng.Row
        End If
        w1.Range("R" & s).Value = w1.Range("R" & s).Value + Application.Sum(w2.Range("O" & r & ":Z" & r))
        For c = 1 To 7
            w1.Cells(s, c + 18).Value = w1.Cells(s, c + 18).Value + w2.Cells(r, c + 26).Value
        Next c
    Next r

    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

HansV wrote:
04 Feb 2021, 13:33
....that code was too complicated for me to analyze
For me it was just it took too long to be worth the effort, or probably more like, due to my low intelligence it meant it took me a lot longer than anyone else would to analyse..
but as I started looking at it.., I decided to finish.
So I went through that macro and its two previous versions from that excelforum post
I understand it finally

There is quite a bit of use of that
arrOut()=AppIndex(arrIn(), Rws(), Clms())
type code line that I personally like to use
( https://eileenslounge.com/viewtopic.php ... 84#p265384 )
So I recognised some parts straight away. Other bits took me a while to figure out what’s going on.

Also, In a few places I see a phenomena that often pops up whereby you get, pseudo like
_____...(…Transpose ..( sdhfadhf …§”$%(& .( Transpose 5597bsbkjybc
In such cases you see doing something , then undoing it
That serves no purpose other than making the code longer, more obscure, and less efficient.
In such a case the two things are a waste of time and this would do the job more efficiently
_____...(… ..( sdhfadhf …§”$%(& .( 5597bsbkjybc

( I am using the Transpose here, just as an example, as it is a typical example of the doing something , then undoing it phenomena which I am talking about

This phenomena which I am talking about , comes up from about 3 reasons usually , I think

_ ! Simply that the macro is left in its first draft form. Someone like maras is a profi giving free help in a forum so he does the thing as he feels like ., That’s his right. Maybe sometimes he does something as quick as possible, then moves on and does not check through as he would if he’d been paid for it. ( he went on to answer a few dozen questions in the time I spent figuring out what his macros for Jim are doing! )

_2 Some of this stuff to do with the area of dictionaries, that arrOut()=AppIndex(arrIn(), Rws(), Clms())
type code line and similar array work, comes from a certain famous persons blog site who I “tangled with” in many places a few times. I have come to the conclusion that whilst giving a lot of great free tuition, this chap also likes in parallel to amuse himself seeing his deliberate mistakes being copied by people. … I often pointed out the mistakes in his blog site, and he just gets upset with me and leaves the mistakes there most of the time…

_3 Some people prefer to have an arsenal of code snippets which they cobble together. Some people prefer not to learn or to write a code based on their understanding.,
Rather than doing that, they write coding very quickly based on instinct and their arsenal of code snippet solutions. It gets them a working code the quickest, answers the question the quickest , and then they move on. That is very noticeable in forums, and it can be , at least partially, linked to _1 – the same people may act differently when they are paid for the same….


Some people prefer not to learn about the thing they work in. That’s cool – the world would be very boring if we were all the same.


_._______

Anyway I understand now all the macros Jim has had, and I probably could answer his last additional question, but probably wont, not yet anyway.

In the next posts I will present my 'marked up versions of the macros he has had from maras along the way, leading up to the one he presented to us here at Eileen’s Lounge

I think that will be good for future reference, mine at least, in case I do decide later to take it further.
I think that would be better , at this stage, since I think Jim is probably in a muddle , so needs to sort himself out first.
!!! More to that in my last post Later:
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

Hello Jim ( and everyone else )
I took a look through…. starting from the beginning.
https://www.excelforum.com/excel-progra ... ost5458446
_1. First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
_2. Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
_3. I wanted to sum from column P to colum AA and transfer those sum to destination at column H
_4. Then copy from column AB to AH and paste in I to O in destination



Initially these two things were basically done, by the first macro from maras, ( which I have added 'comments to, and called Sub Transfer_maras1_1() in the uploaded workbook )_
_ 1 Some columns were taken from a filtered source data range and put into another range in a destination workbook
_ 2 An extra column was added in the new range which was the sum of some other columns in the original range
That’s it , basically.
( By the way, there were some errors and typos in that macro from maras so it would never have worked correctly – it may have worked, but the results would have been slightly in error . I corrected all those errors in my version).


The macro is in my opinion a bit too complicated. My way would have been very similar but just a bit shorter – some things in the macro go around in circles a bit, or they are doing something , then undoing it later

The macro is also doing a lot of extra stuff never asked for. This is because the test data was not too good and the data messed things up a bit … note maras comments…. …. There were anomalies in the 'Workbook2.xlsx'. Many headers had a space at the end, and the main workbook was not.
You need to post attachments on the forum with the same structure as the original. ….

I am not at all criticising maras at all. – I expect my first attempt would have been the same.
After some attempt you saw things weren’t as you wanted , because maras chose to not copy across any columns that did not always match up or if he wasn’t sure what you wanted…

_.__________________-
Here is a summary of the main parts of maras first attempt. Corresponding to posts
https://www.excelforum.com/excel-progra ... ost5458446
https://www.excelforum.com/excel-progra ... ost5458757
and my uploaded version Sub Transfer_maras_1()

Rem 1 the main data range from source
The complete main data range is put in a large array, a() ,
and an array is made , Rws() , for the row indicies of the row numbers of the filtered main range,
and an array is made of the headers, Cls()

Rem 2 building a single column array for the summed colums
An array, asum() , is made and filled with each element being the sum that you wanted for a row. In other words this gives us the column output you want to put in destination column H
While we are in this For Each cel In Rng_v loop, we also fill a 1 dimensional array containing the row indices of all the viswible rows, Rws()

'2a) gets us an array , Cls_v() , containing the column indicies of the columns that you want to take from the source workbook into the destination workbook

'2b) Typical arrOut()=AppIndex(arrIn(), Rws(), Clms())
This is that typical code line which lets us get a sub array from an main array array , based on having arrays containing the row and column indicies numbers of where the row and columns are in the original main array.

arrOut__() is not quite the final array we want. Its condensed missing out a couple of empty columns, so in code section '2c) we pick out the sections we want and put them in the appropriate place. In addition we paste in the sum columns that we got in section Rem 2

_._______________________________

( Note: Some of these following links may be slightly in error. You may need to scroll around pages 41 and 42 at the referenced appendixed Thread to find the referenced screenshots and macros )
Here is a before and after…
https://excelfox.com/forum/showthread.p ... #post15278
https://excelfox.com/forum/showthread.p ... #post15278
https://excelfox.com/forum/showthread.p ... #post15279
https://excelfox.com/forum/showthread.p ... #post15279

Macro
https://excelfox.com/forum/showthread.p ... #post15277
https://excelfox.com/forum/showthread.p ... #post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 12 Feb 2021, 18:06, edited 4 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

To answer this part of your question which came from you, Jim, somewhere along the way… ….
….a. -> Wanted to perform same as highlighted in yellow colour above but with filter 3 not filter 2

The answer is…
I expect you may have realised yourself , that the macros so far written by maras work by taking the filtered rows.
So you just apply the filter, then run the macro.

( the macro part that tells you that , is something of this form
rng_v = rng.Columns(2).SpecialCells(xlCellTypeVisible)
The bit SpecialCells(xlCellTypeVisible tells VBA to just look at the filtered rows, or in other words, it tells VBA to just look at the rows that are visible. )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

On to the story of about these posts …..
https://www.excelforum.com/excel-progra ... ost5459067
https://www.excelforum.com/excel-progra ... ost5459429
and from you word explanation file, Query.docx
_ 1. First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
_ 2. I wanted to transfer in this specific format
_____a. column B(source) to column B (destination) , column C to D, Column D to E, column E to F, Column K to G
____3. I wanted to sum from column O to column Z and transfer those sum to destination at column I
____ 4. Then copy from column AA to AG and paste in j to P in destination
____5. ???


The second macro from maras has just a few minor changes from his first. I have done a version of it with lots of 'comments again , Sub Transfer_marasAlan_2()


The basic idea and code sections are the same as the last macro…
Rem 1 the main data range from source
The complete main data range is put in a large array, a() ,
and an array is made , Rws() , for the row indicies of the row numbers of the filtered main range,
and an array is made of the headers, Cls()

Rem 2 building a single column array for the summed colums
An array, aSum() , is made and filled with each element being the sum that you wanted for a row. In other words this gives us the column output you want to put in destination column I
While we are in this For Each cel In Rng_v loop, we also fill a 1 dimensional array containing the row indices of all the visible rows, Rws()

'2a) gets us an array , Cls_v() , containing the column indicies of the columns that you want to take from the source workbook into the destination workbook

'2b) Typical arrOut()=AppIndex(arrIn(), Rws(), Clms())
This is that typical code line which lets us get a sub array from a main array array , based on having arrays containing the row and column indicies numbers of where those row and columns are in the original main array.

arrOut__() is not quite the final array we want. Its condensed missing out a couple of empty columns, so in code section '2c) we pick out the sections we want and put them in the appropriate place.
In addition, finally, we paste in the sum columns that we got in section Rem 2

_._______________________________

( Note: Some of these following links may be slightly in error. You may need to scroll around pages 41 and 42 at the referenced appendixed Thread to find the referenced screenshots and macros )
Here is a before and after…
https://excelfox.com/forum/showthread.p ... #post15276
https://excelfox.com/forum/showthread.p ... #post15276
https://excelfox.com/forum/showthread.p ... #post15273
https://excelfox.com/forum/showthread.p ... #post15273


Macro
https://excelfox.com/forum/showthread.p ... #post15272
https://excelfox.com/forum/showthread.p ... #post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 12 Feb 2021, 18:07, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfereing data

Post by Doc.AElstein »

Now going on to the code version from here…
https://www.excelforum.com/excel-progra ... ost5459896
https://www.excelforum.com/excel-progra ... ost5460624

That is also the macro version that you, Jim, started with, here with us at Eileen’s Lounge.


So in this post here I am going to work through what the final offering from maras is doing. As before my version ,
Sub Transfer_marasAlan_3(), has a lot of explaining ' comments in it

Your short responses and feedback to both maras and jindon do not convince me that you are 100% sure what is going on any more and / or if the macros really did anything like what you wanted. I have a feeling at some point you had confused yourself.
In actual fact, maras missed out a critical code line of Application.ScreenUpdating = True in his last macro for you. So if you had run his macro, then that should have paralysed your Excel to some extent….
In addition your test data needed correcting ++++
At excelforum you reported that ….. ideas works fine……..
I do not think that is possible. Or at any rate, the ideas may have been sound, but the macros didn’t work….

I think when you posted at Eileen’s lounge you were as confused as maras was.
maras moved on to something else and gave up with you, and
you posted here at Eileen’s Lounge and hoped that by some miracle someone would magically sort out the mess you had got yourself in…. More to that point on the next post !!!!
_._____________________

Anyway, here we go with Sub Transfer_marasAlan_3()



The first bit starts as in the last macros…
Rem 1 the main data range from source
The complete main data range is put in a large array, a()
Then……
' ddddddddddddddddddddddd Dictionaray bit ------
This section basically takes over the previous Rem 2 section, Rem 2 , and does this following bit a bit differently..
Rem 2 building a single column array for the summed columns
Make the sums column array, aSum() , and while we are looping we will collect the seen row ( visible ) indicies, Rws()

An array, aSum() , is made and filled with each element being the sum that you wanted for a row. In other words this gives us the column output you want to put in destination column I
While we are in this For Each cel In Rng_v loop, we also fill a 1 dimensional array containing the row indices of all the visible rows, Rws()


_..... continued in next post…._
Last edited by Doc.AElstein on 06 Feb 2021, 11:53, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also