Jagwarman
asked on
populate Sheets 2 & 3 with data from Sheet 1 using VBA code.
During the time I have been posting on EE I have put some challenging requests out, but this could be the most challenging.
I have attached a file that demonstrates what I need to achieve, so I hope this helps.
Sheet 1 contains the data
Sheets 2 and sheets 3 are the end results.
Static data contains data that Sheets 1 & sheets 2 use.
Information in row 9 – 12 in sheets 1 & 2 is just to show that the above rows must equal zero and is not part of my requirement.
Requirement: The Key is Ref2
I need to populate Sheet 2 & Sheet3 with data from Sheet 1 as per the below Spec. In the cells ‘Batch No’ I need to count number of same “Ref2” and add 1 to this number. i.e. if there are 2 then populate Batch No with 3. If there are 4 populate Batch No with 5.
Spec: Sheet 2
Ccy = Sheet1! C
NOM2 = Sheet1! S
Batch Number = Count of Sheet1! Ref2 Plus 1
Account Number =VLOOKUP(A2,static,2,0)
D/C =IF(B2<0,"D","C")
DATE2 = Sheet1! B
Amount = Sheet1! S
Ref2 = Sheet1! H
Ref1 =ABC
Ref2 =XYZ
Account =D2
D/C =IF(O2<0,"C","D")
DATE2 =F2
NOM = Sheet1! P
Ref2 =H2
Ref1 =ABC
Code Ref = Sheet1! I
Account =D2
D/C =IF(W2>0,"D","C")
DATE2 =F2
NOM = Sheet1! P
Ref2 =H2
Ref1 =ABC
Code Ref =Sheet1!I
Spec: Sheet 3
Ccy = Sheet1! C
NOM2 = Sheet1! N
Batch Number = Count of Sheet1! Ref2 Plus 1
Account Number =VLOOKUP(A2,static,2,0)
D/C =IF(B2<0,"D","C")
DATE2 = Sheet1! B
Amount = Sheet1! N
Ref2 = Sheet1! H
Ref1 =ABC
Ref2 =XYZ
Account =D2
D/C =IF(O2>0,"C","D")
DATE2 =F2
NOM = Sheet1! L
Ref2 =H2
Ref1 =ABC
Code Ref = Sheet1! I
Account =D2
D/C =IF(W2<0,"D","C")
DATE2 =F2
NOM = Sheet1! L
Ref2 =H2
Ref1 =ABC
Code Ref =Sheet1!I
I hope this is self explanitory
Thanks in advance.
I have attached a file that demonstrates what I need to achieve, so I hope this helps.
Sheet 1 contains the data
Sheets 2 and sheets 3 are the end results.
Static data contains data that Sheets 1 & sheets 2 use.
Information in row 9 – 12 in sheets 1 & 2 is just to show that the above rows must equal zero and is not part of my requirement.
Requirement: The Key is Ref2
I need to populate Sheet 2 & Sheet3 with data from Sheet 1 as per the below Spec. In the cells ‘Batch No’ I need to count number of same “Ref2” and add 1 to this number. i.e. if there are 2 then populate Batch No with 3. If there are 4 populate Batch No with 5.
Spec: Sheet 2
Ccy = Sheet1! C
NOM2 = Sheet1! S
Batch Number = Count of Sheet1! Ref2 Plus 1
Account Number =VLOOKUP(A2,static,2,0)
D/C =IF(B2<0,"D","C")
DATE2 = Sheet1! B
Amount = Sheet1! S
Ref2 = Sheet1! H
Ref1 =ABC
Ref2 =XYZ
Account =D2
D/C =IF(O2<0,"C","D")
DATE2 =F2
NOM = Sheet1! P
Ref2 =H2
Ref1 =ABC
Code Ref = Sheet1! I
Account =D2
D/C =IF(W2>0,"D","C")
DATE2 =F2
NOM = Sheet1! P
Ref2 =H2
Ref1 =ABC
Code Ref =Sheet1!I
Spec: Sheet 3
Ccy = Sheet1! C
NOM2 = Sheet1! N
Batch Number = Count of Sheet1! Ref2 Plus 1
Account Number =VLOOKUP(A2,static,2,0)
D/C =IF(B2<0,"D","C")
DATE2 = Sheet1! B
Amount = Sheet1! N
Ref2 = Sheet1! H
Ref1 =ABC
Ref2 =XYZ
Account =D2
D/C =IF(O2>0,"C","D")
DATE2 =F2
NOM = Sheet1! L
Ref2 =H2
Ref1 =ABC
Code Ref = Sheet1! I
Account =D2
D/C =IF(W2<0,"D","C")
DATE2 =F2
NOM = Sheet1! L
Ref2 =H2
Ref1 =ABC
Code Ref =Sheet1!I
I hope this is self explanitory
Thanks in advance.
ASKER
The above are the rules for row 2 [row 1 being the header] but the same needs to apply to every row. There could be 10 rows or 100 rows.
I'm not seeing your sample file. Could you try again to attach it to the message thread? I'm not clear enough from the text what you're trying to do.
ASKER
Information in row 9 – 12 in sheets 1 & 2 should read 2 & 3
Dups.xlsm.xlsx
Dups.xlsm.xlsx
I'm sorry; I've got an unexpectedly heavy workload today. I probably won't be able to look further at this until tonight or tomorrow. One question, though: It looks like you're just selecting the first row in Sheet1 that matches Ref2 and using all its values, so the information on lines 3, 5, &, 8, and 10 is ignored (except for the count of lines). Is that correct, or am I missing something?
ASKER
Hi ElrondCT
Some info from row 3 is used. In sheet2 Column P from sheet1.
730
20
2000
2
Also code Ref from row 3 column I goes into column Z in sheets 2 and 3
Sheet 1 column L Row 3 1825 goes into sheet3 column W
Hope this helps
Thanks
Some info from row 3 is used. In sheet2 Column P from sheet1.
730
20
2000
2
Also code Ref from row 3 column I goes into column Z in sheets 2 and 3
Sheet 1 column L Row 3 1825 goes into sheet3 column W
Hope this helps
Thanks
OK. That wasn't obvious from the original list of relationships. Two questions arise:
1) What happens when there are three or more rows, such as you have for Ref2 #41054423? Are the third and later rows ignored (other than for the count)? I don't see any reference to row 8, for instance.
2) What happens if there is a Ref2 with only one row?
1) What happens when there are three or more rows, such as you have for Ref2 #41054423? Are the third and later rows ignored (other than for the count)? I don't see any reference to row 8, for instance.
2) What happens if there is a Ref2 with only one row?
ASKER
Hi ElrondCT
1) Yes ignore apart from the count. I am told there should never be more than two but I have learned never to trust the user 100%
2) There will always be two rows.however based on my comment above, the Key is is Ref2 so if there is no second row the cells that would normally be populated by that second row would remane blank.
Thanks
1) Yes ignore apart from the count. I am told there should never be more than two but I have learned never to trust the user 100%
2) There will always be two rows.however based on my comment above, the Key is is Ref2 so if there is no second row the cells that would normally be populated by that second row would remane blank.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ElrondCT
Thanks for this. I need to give it a test but I am so busy today with it being month end so if you are ok I will check it out tomorrow before I sign off on it.
Kind regards
Thanks for this. I need to give it a test but I am so busy today with it being month end so if you are ok I will check it out tomorrow before I sign off on it.
Kind regards
Not a problem. I know how month end goes...
ASKER
Feels like it's been a month. All checked and great. Thanks for your help with this one. I will try to make the next one more of a challenge :-)
ASKER
Excellent
ASKER