Transfereing data

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

Re: Transfereing data

Post by Doc.AElstein »

_...from last post….._


' ddddddddddddddddddddddd Dictionaray bit ------
This section basically takes over the previous section, Rem 2 , and does this following bit a bit differently..
Rem 2 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()


This dictionary bit, is the main difference. It’s the modified bit to satisfy a requirement of like….
….1. As I have added column AH and AI …( AI is grandtotal ). ….in the source work book there are some data's having same unique ID ……. There are few circumstance where column B (ID) might have repetition of same ID number and in this case I wanted to check “Grandtotal” in AI column and which ever is higher with the same ID number I wanted to transfer to the other workbook and ignore the other one please…..

In words this is what is going on in this dddd Dictionaray bit ---- section..
We build up a dictionary “list of items”, and there is only one item for every ID. ( The Key for the dictionary item is the ID from column B in the original source data.)
If more than one row of the original source data has a particular ID, then we only use that row which has the largest grandtotal in range AI of the original source data. This is the main reason for us using a dictionary – it is a convenient way to make a unique list of stuff in VBA.
Each Item is a 3 element array. ( we use the same temporarily 3 element array, aTp() to fill up then pass to the dictionary Item )
The first element is the row number
The second element is the grandtotal
The third element is the sum of the columns O to Z

The dictionary.Items() array will give us an unjagged jagged array, - in other words this will be a 1 dimensional array whose elements are themselves a 3 element 1 dimensional array.
Unexpectidly we find that we can apply the Index to get out the “pseudo first and third columns” from such a unjagged jagged array
( https://eileenslounge.com/viewtopic.php ... 91#p266691 )
I am quite impressed that maras knew about that, as I thought we “invented” / discovered that at Eileen’s lounge…. Having said that, maras had some extra unnecessary bits, so he may have got the result by accident or instinct, and so he may not have realised what was going on )


The rest more or less follows the previous 2 macros
'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 ( our a() is the main source data array), based on having arrays containing the row and column indicies numbers of where the row and columns are those related to the original main array. In other words we pick out just a subset of the rows and columns.
The major part of the workings of the macro is based on getting those indicie arrays , Rws() and Cls_v() . – That is usually the main part of any macro which towards the end uses a code line like arrOut()=AppIndex(arrIn(), Rws(), Clms()) to get the final results

arrOut__() is not quite the final array we want. Its condensed , so it is 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 sums column 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 ... #post15269
https://excelfox.com/forum/showthread.p ... #post15270

Macro
https://excelfox.com/forum/showthread.p ... #post15268
https://excelfox.com/forum/showthread.p ... #post15271

Files
https://excelfox.com/forum/showthread.p ... #post15341
https://excelfox.com/forum/showthread.p ... #post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23

++++
Note :
_ I changed header ID in Transfer data_marasAlan_3.xlsm to Unique ID
_ I removed the extra headings after column P in Workbook2_3.xlsx - It is unclear exactly what you want. You need to clarify / or get clear yourself what it is you actually want for an output. – it is always best if you give a hand filled in output worksheet to show exactly what results the macro should give, based on your given test data. Otherwise we are left to guess, and we all end up in a muddle
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:08, 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 »

!!!! At the end of the day, Jim, …
I guess maras just moved on to your next modified requirement, then moved on to your next modified requirement did that on his last macro, and so on, and so on, so it was all a bit of a patch work in the end.
Then he was probably fed up and moved on permanently to something else.

I expect maras would have written his macro a lot differently if he had known from the start all that you wanted in the end.

You probably would be best to forget everything, start again and think very carefully about what you want and be careful to give correct test data. Try to keep the test data to the minimum amount which tests all scenarios
Building up or modifying your requirement almost always leads to lots of extra unnecessary work by those helping you, and almost always ends up with you getting a poor inefficient macro, if you ever get one at all.

_.______

I will leave it at that for now…….. I think me or someone else could do a much simple efficient macro from scratch if you
_ start again after having thought very carefully about what you want
_ explain again clearly what you want
_ give some good representative, but minimum in quantity, test data.
_ give a hand filled in output worksheet to show exactly what results the macro should give, based on your given test data.

_.____

I am slightly reluctant to go on to your last requirement at this stage in case there are other odd little additions you need.
It would be more efficient to be sure of all you want first, and then for me to start again from scratch
I recon that in the last few posts I took more time than I would need to start again from scratch once your final requirements are known for sure…
My macro would be in a similar form to all those from maras: As I mentioned before, the basic ideas he is using are very similar to the preferred way I tend to do these things.

Possibly you understand at the moment what you want. Possibly not.
At any rate I think you will need to explain a lot more carefully if you want further help.

( on the other hand I might have another look later anyway…. Just now I need to go and wash a few tons of gravel with my bare hands for light relief )

Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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

Re: Transfereing data

Post by jimpatel1993 »

Hi all,
Really really thanks for the input. I am really sorry for not following this post for few weeks due to personal health issue. I have attached query and for more details please check attachment.

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

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,
Try and stay healthy, without health nothing else matters…

BTW did you see the stuff from p45 (https://eileenslounge.com/viewtopic.php ... 08#p280308 ) - I can’t comment on that , Power Query is a big mystery to me. It could be a better alternative for you, maybe. I don’t know. )
_._________________-

I have a question and request.

_ (i) In workbook2
Will the “Gap” columns ( C H Q ) always be empty ?

_(ii) Please give me another Workbook 2, call it something like Workbook2before. This workbook should show the situation before the macro runs. ( I am assuming the Workbook2 you have given is the After situation )

( depending on what the Workbook2before looks like, it might help if you say again exactly what the macro should do )

Alan

P.S. based on you latest sample files, I am assuming that you are no longer interested in the stuff from here
https://eileenslounge.com/viewtopic.php ... 47#p280447
https://eileenslounge.com/viewtopic.php ... 48#p280448
In other words, you are no longer interested in , at least not in you current request, the situation of …in the source work book there being some data's having same unique ID….
If you do still want the coding to take care of that situation, then please modify your test data appropriately so that it demonstrates this: Remember your test data should demonstrate all possible scenarios
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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

Re: Transfereing data

Post by jimpatel1993 »

Hi Alan,

Thanks for your message and sure health is more important than anything else.
I have tried different method as you highlighted but it does not work unfortunately.

I have answers for your questions.

1. Those gaps will be there always unfortunately
2. I have attached 2 destination workbook. One destination workbook is after transfering sheet 1 from source workbook. Other one is after transferring sheet 2 from source workbook.

Hope this helps.

Really sorry for asking help and thanks again

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

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,
The question (i) which I meant was if the cells would always be empty. In other words, I wanted to know if they would never have anything in them. For now I will assume that they will always be empty.
_.___________-
jimpatel1993 wrote:
12 Feb 2021, 08:21
I have tried different method as you highlighted but it does not work unfortunately.
?? I am afraid I don’t understand what you are saying there. ??
_._____

Thanks for the two Workbook 2 afters.
It would also have been helpful to have a workbook 2 before
I am not 100% sure I understand all your requirements.
_.__________

Never mind.
I am starting again from scratch. This is because, as I mentioned previously, it is much harder to try to modify someone else’s coding.
In this post I will do a macro to produce your after from Sheet1

Sub Transfer_Sht1After()
Some of the ideas from maras coding is repeated in a much director way, for example,
_ arrays are made in the final required size and orientation , removing the need to resize, or transpose,
and the final
arrOut()=Index(arrIn(), Rws(), Clms)
_ is a single line, as I assume gap columns will always be empty so can be filled with any spare empty column, ( I use column AH ( column number 34 ) )
and
_ Its efficient in this case to use
arrOut()=Index(Sheet1.Cells, Rws(), Clms)
That eliminates the need for an original data array capture

Here is the macro :

Code: Select all

Option Explicit
Sub Transfer_Sht1After() '  https://eileenslounge.com/viewtopic.php?p=280747#p280747
Rem 1 Source Worksheets info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr1 As Long: Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
'1b)  Any column in the visible data is taken in the next code line, the main reason being as we need to get the row indicie info
Dim Rng_v As Range: Set Rng_v = Ws1.Range("B1:B" & Lr1 & "").SpecialCells(xlCellTypeVisible) ' this gets just the range we see, so will be likely a range of lots of areas
    If Rng_v.Count = 1 Then ' case only header range visible
     MsgBox Prompt:="No rows to transfer.": Exit Sub
    Else ' there are visible rows to transfer
    Rem 2 building a single column array for the summed colums, and the wanted visible row indicies from the main range
    Dim aSum() As Variant: ReDim aSum(1 To Rng_v.Count - 1, 1 To 1) '  This will be a  column  array when applied to a worksheet
    Dim Rws() As Long: ReDim Rws(1 To Rng_v.Count - 1, 1 To 1) ' we need a  "virtical"  array containing the "seen" row indicies
    Dim Cel As Range
        For Each Cel In Rng_v '  These are the cells in the multi  Area  range of visible cells
            If Cel.Row > 1 And Cel.Value <> "" Then
            Dim I As Long
             Let I = I + 1
             Let aSum(I, 1) = Evaluate("=Sum('[" & ThisWorkbook.Name & "]Sheet1'!O" & Cel.Row & ":'[" & ThisWorkbook.Name & "]Sheet1'!Z" & Cel.Row & ")")
             Let Rws(I, 1) = Cel.Row '  This  puts the visible rows indicie in our array indicationg the rows we need from the worksheet
            Else
            End If
        Next Cel
    End If
' Destination workbook and worksheet
Dim Pth As String: Let Pth = ThisWorkbook.Path & Application.PathSeparator '  Const Pth = "C:\Users\L026936\Desktop\Excel\"      '<---- use own path
Const Wnm = "Workbook2_2b.xlsx"              'your destination workbook2  name
On Error Resume Next                                     '     https://eileenslounge.com/viewtopic.php?f=30&t=35861&start=20
Dim WbDest As Workbook
 Set WbDest = Workbooks(Wnm) ' will error if workbook is not yet open
    If Err.Number > 0 Then
     Workbooks.Open Filename:=Pth & Wnm ' we test to see if we have an error and if we do themn we kknow to open the workbook    On Error GoTo 0
     Set WbDest = ActiveWorkbook
    Else
    End If
''2a) Column indicies of the columns wanted from the data worksheet
Dim Clms() As Variant: Let Clms() = Array(2, 34, 3, 4, 5, 11, 34, 34, 27, 28, 29, 30, 31, 32, 33)
'2b) Typical arrOut()=AppIndex(arrIn(), Rws(), Clms())
 Let WbDest.Worksheets.Item(1).Range("B2").Resize(UBound(Rws(), 1), 15).Value2 = Application.Index(Ws1.Cells, Rws(), Clms())
'2c)(ii) Sums column
 Let WbDest.Worksheets.Item(1).Range("B2").Resize(UBound(Rws(), 1), 1).Offset(0, 7).Value2 = aSum()
End Sub
The above macro does more or less the same as the first few macros that maras did for you at excelforum.com

Here is the befores screenshots:
https://excelfox.com/forum/showthread.p ... #post15259
https://excelfox.com/forum/showthread.p ... #post15232

This is what you get after running the macro

https://excelfox.com/forum/showthread.p ... #post15341

_.___________________-

I will take a look at the macro for after Sheet 2 later or tomorrow

Alan
You do not have the required permissions to view the files attached to this post.
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..
On to
The AfterSht2 situation

I think the information given so far from you is incomplete. Or as time as gone on, I may have missed something. In any case, i have had to spend some time trying to figure out what you want:
So this is what I have understood and/or assumed or guessed so far:-

' The AfterSht2 situation'
' The source Sht2 data transfer to destination worksheet, is considered after source Sht1 transfer to destination worksheet is finished with
' I assume that my last macro, Sub Transfer_Sht1After() , is doing what you want and filling in the destination Worksheet as it should be.
'
' Most of the following I have established from looking at the data so far supplied.

' We have broadly 3 scenarios:
' _1 some unique IDs in source Sht2 will match unique IDs in/from source Sht1
' ( In other words, some of the unique IDs in the source Sht2 will already be in the destination worksheet )
' _2 some unique IDs from source Sht1 will not be in source Sht2.
' (In other words some unique IDs already in the destination worksheet are not in the source Sht2
' _3 there will be some new unique IDs in source Sht2
' ( In other words, there will be some unique IDs is source Sht2 which are not in destination worksheet )

' Depending on the 3 scenarios, different things need to be done
' _1 some unique IDs in Sht2 will match unique IDs in Sht1
' we copy cells values from source sht2 to destination worksheet in the matching ID row in the destination worksheet
' from - to columns
' AA - T
' AB - U
' AC - V
' AD - W
' AE - X
' AF - Y
' AG - Z
' K - R

' _2 some unique IDs from source Sht1 will not be in source Sht2
' previous information in destination worksheet gets copies across to the right. So in the same row in destination worksheet we copy cells in following columns
' from - to columns
' J - T
' K - U
' L - V
' M - W
' N - X
' O - Y
' P - Z
' G - R

' _3 there will be some new unique IDs in source Sht2
' new rows will be added to the destination worksheet. To fill these rows with data, similar cell values are copied across as in _1)
' from - to columns
' AA - T
' AB - U
' AC - V
' AD - W
' AE - X
' AF - Y
' AG - Z
' K - R


_.____________________________


Jim , I need 3 bits of information from you

_ a) Is what I have written above correct? If not tell me what I have got wrong

_b) Where do those total figures come from which you showed in column S of the destination worksheet? ( make sure you tell me where they come from for each of the 3 scenarios )

_c) Is there anything else I have missed or which I should know?

Alan

P.S. 1. Uploaded , just for info, is a “help” worksheet which I have been using to try and figure out what you want.

P.S. 2: Some other information is less important, but might be useful to know. This is information related to what typical real data might look like in the future …_

For example:

_d) what might be typical figure for the maximum number of unique IDs that you might ever have?

_e) Are you likely to want to extend your destination worksheet data further to the right?
_….this sort of information is important to know as soon as possible. Otherwise if you need modifications later then this can lead to very messy and inefficient codings , and lots of time wasting, as you have already seen: Extending coding is not like building a house higher and higher by just adding more bricks on top of the other ones alrteady there. When you add new coding requirements, then it often means all previous coding is useless and you have to start all over again…
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also