Build a duplicate data transfer button from a table in sheet one to a table designed in sheet two and specify each person in a row

bahrami

New Member
Joined
Feb 26, 2022
Messages
14
Office Version
  1. 2007
Platform
  1. Windows
Hello, if possible, please check the attachment with vba.
1- Table 1 has a list of people who have been present in the board of directors repeatedly or for several consecutive or alternate periods.
2- In sheet 2, the table is designed to transfer each person's election information in a row in front of the person's information with one button.
Thank you for your help
Example file-1400.xlsm
ABCDEFGHI
1RowNational Code namelast nameBoard codeUnion nameBoard codeSide Election date
21181-849862-6امینتوانائیfirst roundTools Union1boss1391/03/27
32181-939349-6عبدالرضاشیرانی پور اصفهانیfirst roundTools Union2First Vice President1391/03/27
43175-379165-0امیرابراهیم زادهfirst roundTools Union4Secretary1391/03/27
54181-832809-7حمیدرضاابراهیمی عسکریfirst roundTools Union5Treasurer1391/03/27
65181-961857-9شهرامشوقی آزادfirst roundTools Union6Inspector1391/03/27
76181-849862-6امینتوانائی second periodTools Union1boss1395/02/26
87181-939349-6عبدالرضاشیرانی پور اصفهانی second periodTools Union2First Vice President1395/02/26
98175-379165-0امیرابراهیم زاده second periodTools Union3Second Vice President1395/02/26
109181-961857-9شهرامشوقی آزاد second periodTools Union4Secretary1395/02/26
1110181-832809-7حمیدرضاابراهیمی عسکری second periodTools Union5Treasurer1395/02/26
1211181-735988-6علیرضاشقایق مهر second periodTools Union6Inspector1395/02/26
1312181-818343-9حمیدرضاابراهیمی عسکریThird periodTools Union1boss1399/11/06
1413181-939349-6عبدالرضاشیرانی پور اصفهانیThird periodTools Union2First Vice President1399/11/06
1514175-379165-0امیرابراهیم زادهThird periodTools Union3Second Vice President1399/11/06
1615181-832809-7 حمیدرضاابراهیمی عسکریThird periodTools Union4Secretary1399/11/06
1716181-961857-9شهرامشوقی آزادThird periodTools Union5Treasurer1399/11/06
1817174-152420-2محمدابراهیم زادهThird periodTools Union6Inspector1399/11/06
1918181-717714-1فریدفاطمی زادهfirst roundElectric Trade Union1boss1389/10/08
2019181-715665-9وحیدفاطمی زادهfirst roundElectric Trade Union2First Vice President1389/10/08
2120181-908575-9جمشیدرضوانfirst roundElectric Trade Union4Secretary1389/10/08
2221181-830153-9امیدسوزنگرزادهfirst roundElectric Trade Union5Treasurer1389/10/08
2322181-746082-1محمودجعفریfirst roundElectric Trade Union6Inspector1389/10/08
2423181-717714-1فریدفاطمی زاده second periodElectric Trade Union1boss1394/10/14
2524181-715665-9 وحیدفاطمی زاده second periodElectric Trade Union2First Vice President1394/10/14
2625181-746082-1محمودجعفری second periodElectric Trade Union3Second Vice President1394/10/14
2726181-908575-9جمشیدرضوان second periodElectric Trade Union4Secretary1394/10/14
2827181-830153-9 امیدسوزنگرزاده second periodElectric Trade Union5Treasurer1394/10/14
2928007-365682-8وحید آشنا second periodElectric Trade Union6Inspector1394/10/14
3029181-717714-1فریدفاطمی زادهThird periodElectric Trade Union1boss1399/10/22
3130181-715665-9وحیدفاطمی زادهThird periodElectric Trade Union2First Vice President1399/10/22
3231181-746082-1محمود جعفریThird periodElectric Trade Union3Second Vice President1399/10/22
3332181-908575-9جمشیدرضوانThird periodElectric Trade Union4Secretary1399/10/22
3433181-830153-9امیدسوزنگرزادهThird periodElectric Trade Union5Treasurer1399/10/22
3534007-365682-8وحید آشناThird periodElectric Trade Union6Inspector1399/10/22
36993181-842934-1بهمنحسین زاهFourth periodIce Cream Guild Union1boss1400/02/30
37994181-842934-9اصغرصباحFourth periodIce Cream Guild Union2First Vice President1400/02/30
38995181-842935-7حسینگودرزیFourth periodIce Cream Guild Union3Second Vice President1400/02/30
39996181-842936-5محمدکیانیFourth periodIce Cream Guild Union4Secretary1400/02/30
40997181-842937-3احمدقلی کارگرFourth periodIce Cream Guild Union5Treasurer1400/02/30
41998181-842938-1سامانکیانیFourth periodIce Cream Guild Union6Inspector1400/02/30
42999181-849862-6امینتوانائیFourth periodTools Union1boss1391/03/27
431000181-939349-6عبدالرضاشیرانی پور اصفهانیFourth periodTools Union2First Vice President1391/03/27
441001175-379165-0امیرابراهیم زادهFourth periodTools Union4Secretary1391/03/27
451002181-832809-7حمیدرضاابراهیمی عسکریFourth periodTools Union5Treasurer1391/03/27
461003181-961857-9شهرامشوقی آزادFourth periodTools Union6Inspector1391/03/27
471004181-849862-6امینتوانائیFourth periodTools Union1boss1395/02/26
Sheet1

Example file-1400.xlsm
ABCDEFGHIJKLMNO
1List of board members
2RowSpecificationsBoard codeMore than two or four alternating periodsNumber of company courses in the board of directors
3Union namenamelast nameNational CodeSide of the first periodDate of the first periodSide of the second periodDate of the first periodSide of the ThirdperiodDate of the ThirdperiodSide of the Fourth periodDate of the Fourth period
41Tools Unionمحمدابراهیمی عسکری181-818343-910000boss1399/11/06001
52Tools Unionامینتوانائی181-849862-61boss1391/03/27boss1395/02/3000002
63Tools Unionعبدالرضاشیرانی پور اصفهانی181-939349-62First Vice President1391/03/27First Vice President1395/02/30First Vice President1399/11/06003
74Tools Unionامیرابراهیم زاده175-379165-04Secretary1389/08/08Second Vice President1394/08/17Second Vice President1399/11/06003
85Tools Unionحمیدرضاابراهیمی عسکری181-832809-75Treasurer1389/08/08Treasurer1394/08/17Secretary1399/11/06003
96Tools Unionعلیرضاشقایق مهر181-735988-6601389/08/08Inspection1394/08/1700001
107Tools Unionمحمدابراهیم زاده174-152420-260000Inspection1399/11/06001
118Tools Unionشهرامشوقی آزاد181-961857-96Inspector1389/08/08Treasurer1394/08/17Treasurer1399/11/06003
129Electric Trade Unionفریدفاطمی زاده181-717714-11boss1389/10/08boss1394/10/14boss1399/10/22003
1310Electric Trade Union وحیدفاطمی زاده181-715665-92First Vice President1389/10/08First Vice President1394/10/14First Vice President1399/10/22003
1411Electric Trade Unionجمشیدرضوان181-908575-94Secretary1389/10/08Secretary1394/10/14Secretary1399/10/22003
1512Electric Trade Unionامیدسوزنگرزاده181-830153-95Treasurer1389/10/08Treasurer1394/10/14Treasurer1399/10/22003
1613Electric Trade Unionوحید آشنا007-365682-8600Inspection1394/10/14Inspection1399/10/22002
1714Electric Trade Unionمحمودجعفری181-746082-16Inspection1389/10/08First Vice President1394/10/14First Vice President1399/10/22003
Sheet2
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Your Sheet2 contains merged cells. Please re-design it eliminating all the merged cells. You should avoid merging cells because they almost always cause problem for macros. Sheet1 also has some inconsistencies in column E with the use of "round" in some cells and "period" in other cells. In Sheet1, National Code 181-849862-6 has 2 Fourth Period board codes. Is this possible? Please review your data and make the necessary correction. The re-post your corrected data.
 
Upvote 0
Solution
Your Sheet2 contains merged cells. Please re-design it eliminating all the merged cells. You should avoid merging cells because they almost always cause problem for macros. Sheet1 also has some inconsistencies in column E with the use of "round" in some cells and "period" in other cells. In Sheet1, National Code 181-849862-6 has 2 Fourth Period board codes. Is this possible? Please review your data and make the necessary correction. The re-post your corrected data.
Thank you
 
Upvote 0
Hello, while appreciating your attention, the column includes people who have participated in the elections for several terms and the duplicate national code belongs to the same people.
In short, we want to transfer the information of the people who participated in several courses based on the national code, the name, the name of the union and the courses they participated in, and the direction and date of the elections in one row in sheet 2. thanks for your help.

Example file-1401.xlsm
ABCDEFGHI
1RowNational Code namelast nameBoard codeUnion nameBoard codeSide Election date
21181-849862-6امینتوانائیfirst roundTools Union1boss1391/03/27
32181-939349-6عبدالرضاشیرانی پور اصفهانیfirst roundTools Union2First Vice President1391/03/27
43175-379165-0امیرابراهیم زادهfirst roundTools Union4Secretary1391/03/27
54181-832809-7حمیدرضاابراهیمی عسکریfirst roundTools Union5Treasurer1391/03/27
65181-961857-9شهرامشوقی آزادfirst roundTools Union6Inspector1391/03/27
76181-849862-6امینتوانائی second periodTools Union1boss1395/02/26
87181-939349-6عبدالرضاشیرانی پور اصفهانی second periodTools Union2First Vice President1395/02/26
98175-379165-0امیرابراهیم زاده second periodTools Union3Second Vice President1395/02/26
109181-961857-9شهرامشوقی آزاد second periodTools Union4Secretary1395/02/26
1110181-832809-7حمیدرضاابراهیمی عسکری second periodTools Union5Treasurer1395/02/26
1211181-735988-6علیرضاشقایق مهر second periodTools Union6Inspector1395/02/26
1312181-818343-9حمیدرضاابراهیمی عسکریThird periodTools Union1boss1399/11/06
1413181-939349-6عبدالرضاشیرانی پور اصفهانیThird periodTools Union2First Vice President1399/11/06
1514175-379165-0امیرابراهیم زادهThird periodTools Union3Second Vice President1399/11/06
1615181-832809-7 حمیدرضاابراهیمی عسکریThird periodTools Union4Secretary1399/11/06
1716181-961857-9شهرامشوقی آزادThird periodTools Union5Treasurer1399/11/06
1817174-152420-2محمدابراهیم زادهThird periodTools Union6Inspector1399/11/06
1918181-717714-1فریدفاطمی زادهfirst roundElectric Trade Union1boss1389/10/08
2019181-715665-9وحیدفاطمی زادهfirst roundElectric Trade Union2First Vice President1389/10/08
2120181-908575-9جمشیدرضوانfirst roundElectric Trade Union4Secretary1389/10/08
2221181-830153-9امیدسوزنگرزادهfirst roundElectric Trade Union5Treasurer1389/10/08
2322181-746082-1محمودجعفریfirst roundElectric Trade Union6Inspector1389/10/08
2423181-717714-1فریدفاطمی زاده second periodElectric Trade Union1boss1394/10/14
2524181-715665-9 وحیدفاطمی زاده second periodElectric Trade Union2First Vice President1394/10/14
2625181-746082-1محمودجعفری second periodElectric Trade Union3Second Vice President1394/10/14
2726181-908575-9جمشیدرضوان second periodElectric Trade Union4Secretary1394/10/14
2827181-830153-9 امیدسوزنگرزاده second periodElectric Trade Union5Treasurer1394/10/14
2928007-365682-8وحید آشنا second periodElectric Trade Union6Inspector1394/10/14
3029181-717714-1فریدفاطمی زادهThird periodElectric Trade Union1boss1399/10/22
3130181-715665-9وحیدفاطمی زادهThird periodElectric Trade Union2First Vice President1399/10/22
3231181-746082-1محمود جعفریThird periodElectric Trade Union3Second Vice President1399/10/22
3332181-908575-9جمشیدرضوانThird periodElectric Trade Union4Secretary1399/10/22
3433181-830153-9امیدسوزنگرزادهThird periodElectric Trade Union5Treasurer1399/10/22
3534007-365682-8وحید آشناThird periodElectric Trade Union6Inspector1399/10/22
36999181-849862-6امینتوانائیFourth periodTools Union1boss1391/03/27
371000181-939349-6عبدالرضاشیرانی پور اصفهانیFourth periodTools Union2First Vice President1391/03/27
381001175-379165-0امیرابراهیم زادهFourth periodTools Union4Secretary1391/03/27
391002181-832809-7حمیدرضاابراهیمی عسکریFourth periodTools Union5Treasurer1391/03/27
401003181-961857-9شهرامشوقی آزادFourth periodTools Union6Inspector1391/03/27
411004181-849862-6امینتوانائیFourth periodTools Union1boss1395/02/26
42993181-842934-1بهمنحسین زاهFourth periodIce Cream Guild Union1boss1400/02/30
43994181-842934-9اصغرصباحFourth periodIce Cream Guild Union2First Vice President1400/02/30
44995181-842935-7حسینگودرزیFourth periodIce Cream Guild Union3Second Vice President1400/02/30
45996181-842936-5محمدکیانیFourth periodIce Cream Guild Union4Secretary1400/02/30
46997181-842937-3احمدقلی کارگرFourth periodIce Cream Guild Union5Treasurer1400/02/30
47998181-842938-1سامانکیانیFourth periodIce Cream Guild Union6Inspector1400/02/30
Sheet1


Example file-1401.xlsm
EFGHIJKLMNO
1National CodeBoard codeSide of the first periodDate of the first periodSide of the second periodDate of the first periodSide of the ThirdperiodDate of the ThirdperiodSide of the Fourth periodDate of the Fourth periodNumber of company courses in the board of directors
2181-818343-910000boss1399/11/06001
3181-849862-61boss1391/03/27boss1395/02/3000002
4181-939349-62First Vice President1391/03/27First Vice President1395/02/30First Vice President1399/11/06003
5175-379165-04Secretary1389/08/08Second Vice President1394/08/17Second Vice President1399/11/06003
6181-832809-75Treasurer1389/08/08Treasurer1394/08/17Secretary1399/11/06003
7181-735988-6601389/08/08Inspection1394/08/1700001
8174-152420-260000Inspection1399/11/06001
9181-961857-96Inspector1389/08/08Treasurer1394/08/17Treasurer1399/11/06003
10181-717714-11boss1389/10/08boss1394/10/14boss1399/10/22003
11181-715665-92First Vice President1389/10/08First Vice President1394/10/14First Vice President1399/10/22003
12181-908575-94Secretary1389/10/08Secretary1394/10/14Secretary1399/10/22003
13181-830153-95Treasurer1389/10/08Treasurer1394/10/14Treasurer1399/10/22003
14007-365682-8600Inspection1394/10/14Inspection1399/10/22002
15181-746082-16Inspection1389/10/08First Vice President1394/10/14First Vice President1399/10/22003
Sheet2
 
Upvote 0
In the Report sheet, place the headers starting with National Code in cell A1 not E1. Then in F1, correct "Date of the first period" to "Date of the second period". In Sheet1, some of the Board Codes in column E have leading and/or trailing spaces. Remove all the trailing spaces in the codes in column E. Then try this macro:
VBA Code:
Sub TransferData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, v As Variant
    Dim i As Long, rCount As Long, fnd As Range, rng As Range, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Report")
    lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = srcWS.Range("A1").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(v, 1)
        If Not dic.Exists(v(i, 2)) Then
            dic.Add v(i, 2), Nothing
            With srcWS
                .Range("A1").CurrentRegion.AutoFilter 2, v(i, 2)
                rCount = .[subtotal(103,A:A)] - 1
                With desWS
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    Set fnd = .Rows(1).Find(Split(v(i, 5), " ")(0), LookIn:=xlValues, lookat:=xlPart)
                    .Range("A" & lRow + 1).Resize(, 2).Value = Array(v(i, 2), v(i, 7))
                    For Each rng In srcWS.Range("H2:H" & lastRow).SpecialCells(xlCellTypeVisible)
                        .Cells(lRow + 1, fnd.Column + x).Resize(, 2).Value = Array(rng, rng.Offset(, 1))
                        x = x + 2
                    Next rng
                    .Range("K" & lRow + 1) = rCount
                    x = 0
                End With
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
In the Report sheet, place the headers starting with National Code in cell A1 not E1. Then in F1, correct "Date of the first period" to "Date of the second period". In Sheet1, some of the Board Codes in column E have leading and/or trailing spaces. Remove all the trailing spaces in the codes in column E. Then try this macro:
VBA Code:
Sub TransferData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, v As Variant
    Dim i As Long, rCount As Long, fnd As Range, rng As Range, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Report")
    lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = srcWS.Range("A1").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(v, 1)
        If Not dic.Exists(v(i, 2)) Then
            dic.Add v(i, 2), Nothing
            With srcWS
                .Range("A1").CurrentRegion.AutoFilter 2, v(i, 2)
                rCount = .[subtotal(103,A:A)] - 1
                With desWS
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    Set fnd = .Rows(1).Find(Split(v(i, 5), " ")(0), LookIn:=xlValues, lookat:=xlPart)
                    .Range("A" & lRow + 1).Resize(, 2).Value = Array(v(i, 2), v(i, 7))
                    For Each rng In srcWS.Range("H2:H" & lastRow).SpecialCells(xlCellTypeVisible)
                        .Cells(lRow + 1, fnd.Column + x).Resize(, 2).Value = Array(rng, rng.Offset(, 1))
                        x = x + 2
                    Next rng
                    .Range("K" & lRow + 1) = rCount
                    x = 0
                End With
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
Thank you very much
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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