Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

**DocAElstein** · 05-15-2020, 08:18 PM

( This post is https://excelfox.com/forum/showthrea...ll=1#post13695 )

Some notes related to these posts

https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364

Field1

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

Field11

Data Files

F1

F2

F3

F4

F5

F6

F7

F8

F9

F10

F11

Row1

Line1

Record1

USA

101010

6

<

12783

A

GTT

Row2

Line2

Record2

USA

22

6

<

12783

A

GTT

Row3

Line3

Record3

USA

17388

6

<

12783

A

GTT

Row4

Line4

Record4

USA

100

6

<

12783

A

GTT

Row5

Line5

Record5

USA

25

6

<

12783

A

GTT

Data files are held in computer memory in different forms and retrieved in different ways. Any particular value may be referrenced in many different ways.

**DocAElstein** · 05-15-2020, 08:18 PM

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706

Later

**DocAElstein** · 05-15-2020, 08:18 PM

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706

Later

**DocAElstein** · 05-15-2020, 08:18 PM

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706

Later

**DocAElstein** · 05-18-2020, 01:19 PM

In support of this post:
https://excelfox.com/forum/showthrea...pplied-over-it

_____ Workbook: address sheet.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G
1	Address	Door#	Direction	street name	roadtype	street name + roadtype	City Name
2	204 6 AVE NW	204	6	AVE	NW
3	2510 5 AVE N	2510	5	AVE	N
4	1 CICADA RD	1		CICADA	RD
5	100 annacis Pkwy	100		annacis	Pkwy
6	100 MAIN ST	100		MAIN	ST
7	10008 107 ST	10008		107	ST
8	1001 110 AVE	1001		110	AVE
9	10010 102A AVE NW	10010		102A AVE	NW
10	10115 110 AVE	10115		110	AVE
11	102 11 AVE S	102	S	11	AVE
12	10205 134 AVE NW	10205		134 AVE	NW
13	10235 101 ST NW	10235		101 ST	NW
14	10365 97 ST NW	10365		97 ST	NW
15	105 MARTIN ST	105		MARTIN	ST
16	10504 100 AVE	10504		100	AVE
17	10600 100 ST	10600		100	ST

Worksheet: Sheet1

**DocAElstein** · 05-18-2020, 03:30 PM

Some notes in support in answering this question: https://excelfox.com/forum/showthrea...ata-if-matches

If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to sheet 1 of 2.xlsx in the row of the matched value in column A of sheet 1 of 2.xlsx
i have pasted the result in sheet3 of 2.xlsx but the result should be in sheet1(I have pasted the result in sheet3 only for understanding purpose)

Before:

If column J has data in actual file.xlsx then match column B of actual file.xlsx
_____ Workbook: Actual File.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	65	65.35	60.55	63.3	63.3	1
3	NSE	BANKBARODA	EQ	62.1	62.95	56.15	56.65	56.65	1
4	NSE	BEL	EQ	66.15	66.75	62.4	65.65	65.65	1
5	NSE	EQUITAS	EQ	82	82.05	71	73.05	73.05	1
6	NSE	FEDERALBNK	EQ	68	68.45	62.45	63.1	63.1	1
7	NSE	GAIL	EQ	85	88.8	79.1	79.95	79.95	1
8	NSE	IDFCFIRSTB	EQ	32.1	32.35	27.2	27.55	27.55

Worksheet: Sheet1

_.................If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx
_____ Workbook: 2 18May.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A
1	Stock Name
2	ACC
3	ADANIENT
4	ADANIPORTS
5	ASHOKLEY
6	EQUITAS
7	L&TFH
8

Worksheet: Sheet1

If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to sheet 1 of 2.xlsx
_____ Workbook: 2 18May.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6	7	8	9	10

Worksheet: Sheet2

_.......copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to sheet 1 of 2.xlsx
i have pasted the result in sheet3 of 2.xlsx but the result should be in sheet1(I have pasted the result in sheet3 only for understanding purpose)

After:

_____ Workbook: 2 18May.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	1	2	3	4	5	6	7	8	9	10
6	EQUITAS	1	2	3	4	5	6	7	8	9	10
7	AMBUJACEM	105	117	125	133	140	746	23	80	96	102	109
8

Worksheet: Sheet3

**DocAElstein** · 05-18-2020, 06:20 PM

macro for solution to this Thread:
https://excelfox.com/forum/showthrea...ata-if-matches

( Remember to include Public Function CL() )

Code:

Sub CopyPaste20()  '   https://excelfox.com/forum/showthread.php/2494-Copy-and-paste-of-data-if-matches
Rem 1 Worksheets info
' 2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
Dim Rng22 As Range: Set Rng22 = Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

' Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

Rem 2 do it
Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
    For Cnt = 2 To Jmax
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        If IsError(MtchRes) Then
        ' no match  do nothing
        Else ' Cnt is now at the row number of where  2.xlsx sheet1 column A  was found in  Actual File.xlsx sheet1 column B
        Dim Lc1Cnt As Long: Let Lc1Cnt = Ws1.Cells.Item(Cnt, Ws1.Columns.Count).End(xlToLeft).Column
         Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").ClearContents ' clear row Cnt of all data before pasting
         Rng22.Copy Destination:=Ws1.Range("B" & Cnt & "")                ' copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to the row in  sheet 1 of 2.xlsx  at the row number of the matched value of 2.xlsx sheet1
        End If
    Next Cnt
End Sub

'     http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

**DocAElstein** · 05-19-2020, 02:57 AM

Notes for question 2 here
https://excelfox.com/forum/showthrea...ll=1#post13379
https://excelfox.com/forum/showthrea...ll=1#post13387

Before is as here ,
https://excelfox.com/forum/showthrea...ll=1#post13382
, but ignore Sheet2 - no row is to be copied

If column J has data in actual file.xlsx then match column B of actual file.xlsx

_____ Workbook: Actual File.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	65	65.35	60.55	63.3	63.3	1
3	NSE	BANKBARODA	EQ	62.1	62.95	56.15	56.65	56.65	1
4	NSE	BEL	EQ	66.15	66.75	62.4	65.65	65.65	1
5	NSE	EQUITAS	EQ	82	82.05	71	73.05	73.05	1
6	NSE	FEDERALBNK	EQ	68	68.45	62.45	63.1	63.1	1
7	NSE	GAIL	EQ	85	88.8	79.1	79.95	79.95	1
8	NSE	IDFCFIRSTB	EQ	32.1	32.35	27.2	27.55	27.55
9	NSE	IOC	EQ	93	93.65	87.25	87.9	87.9
10	NSE	L&TFH	EQ	90	91.55	80.5	81.65	81.65
11

Worksheet: Sheet1 (2)

_.................If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx

_____ Workbook: 2 (2).xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	1	2	3	4	5	16	137	177	10	50	93	99	104
6	EQUITAS	10	50	93	99	5	102	110	122	9	10	11
7	L&TFH	11	12	13	14	15	16	17	18	19	20	21	22	23
8

Worksheet: Sheet1

If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then double the value of that row of 2.xlsx

After

_____ Workbook: 2 (2).xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	2	4	6	8	10	32	274	354	20	100	186	198	208
6	EQUITAS	20	100	186	198	10	204	220	244	18	20	22
7	L&TFH	22	24	26	28	30	32	34	36	38	40	42	44	46
8

Worksheet: Sheet2

Note: I think your supplied After is wrong! - L&TFH should not be considered from Actual File.xlsx, because J of that row is not 1

**DocAElstein** · 05-19-2020, 03:26 AM

Macro for last post

Code:

Sub CopyPaste20Q2()  ' Question 2  https://excelfox.com/forum/showthread.php/2494-Copy-and-paste-of-data-if-matches
'    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13388&viewfull=1#post13388
Rem 1 Worksheets info
' 2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
 ' Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
' Dim Rng22 As Range: Set Rng22 =   Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

' Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

Rem 2 do it
Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
    For Cnt = 2 To Jmax
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        If IsError(MtchRes) Then
        ' no match  do nothing
        Else ' Cnt is now at the row number of where  2.xlsx sheet1 column A  was found in  Actual File.xlsx sheet1 column B
        Dim Lc1Cnt As Long: Let Lc1Cnt = Ws1.Cells.Item(Cnt, Ws1.Columns.Count).End(xlToLeft).Column
        ' Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").ClearContents ' clear row Cnt of all data before pasting
        ' Rng22.Copy Destination:=Ws1.Range("B" & Cnt & "")                ' copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to the row in  sheet 1 of 2.xlsx  at the row number of the matched value of 2.xlsx sheet1
         Let Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Value = Ws1.Evaluate("=2*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "") '   then double the value of that row of 2.xlsx
        End If
    Next Cnt
End Sub

**DocAElstein** · 05-19-2020, 03:08 PM

Macro for this post:
https://excelfox.com/forum/showthrea...ll=1#post13397

Code:


Sub ConditionalCalcPaste()   '    https://excelfox.com/forum/showthread.php/2495-Conditional-calculation-and-pasting-of-the-data
Rem 1 Worksheets info
'1a)  2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
'Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
'Dim Rng22 As Range: Set Rng22 = Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

'1b) Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row                                                 ' Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row

Dim rngIn As Range: Set rngIn = Ws.Range("A1:S" & Lr & "")
Dim arrIn() As Variant, arrOut() As Variant: Let arrIn() = rngIn.Value2
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Lr & "").Value2            '  Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

'1c '  calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then
Dim SomeQ As Double: Let SomeQ = Ws.Evaluate("=SUM(Q2:Q" & Lr & ")") '   total value of column Q of ActualFile.xlsx
 Let SomeQ = Application.WorksheetFunction.Round(SomeQ, 2)
Dim S10Val As Double: Let S10Val = arrIn(10, 19)                     '   S10 of ActualFile.xlsx
    If SomeQ > S10Val Then      '  total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then do nothing
    ' do nothing
    ElseIf SomeQ < S10Val Then  ' if it is lower than S10 of ActualFile.xlsx then divide S10 of ActualFile.xlsx with the total value of Column Q of ActualFile.xlsx
    Dim S10dQ As Double: Let S10dQ = S10Val / SomeQ ' Divide S10 of ActualFile.xlsx with the total value of Column Q of ActualFile.xlsx
      Let S10dQ = Int(S10dQ) ' Application.WorksheetFunction.Round(S10dQ, 4)
    Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        For Cnt = 2 To Lr1 '                               Jmax
        Dim MtchRes As Variant
         Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
            If IsError(MtchRes) Then
            ' no match  do nothing
            Else ' Cnt is now at the row number of where  2.xlsx sheet1 column A  was found in  Actual File.xlsx sheet1 column B
            Dim Lc1Cnt As Long: Let Lc1Cnt = Ws1.Cells.Item(Cnt, Ws1.Columns.Count).End(xlToLeft).Column
            ' Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").ClearContents ' clear row Cnt of all data before pasting
            ' Rng22.Copy Destination:=Ws1.Range("B" & Cnt & "")                ' copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to the row in  sheet 1 of 2.xlsx  at the row number of the matched value of 2.xlsx sheet1
             Let Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Value = Ws1.Evaluate("=" & S10dQ & "*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "")  ' Ws1.Evaluate("=2*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "") '   then double the value of that row of 2.xlsx
            End If
        Next Cnt
    Else
    ' Sum = S10
    End If ' SumQ>S10
End Sub