Appendix Thread. ( Codes for other Threads, ( Avinash ).)

Printable View

Show 40 post(s) from this thread on one page

Text File

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

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

Text Files

USA

;

101010

;

6

;

<

;

12783

;

A

;

;

;

;

;

GTT

LineSeprator

USA

;

22

;

6

;

<

;

12783

;

A

;

;

;

;

;

GTT

LineSeprator

USA

;

17388

;

6

;

<

;

12783

;

A

;

;

;

;

;

GTT

LineSeprator

USA

;

100

;

6

;

<

;

12783

;

A

;

;

;

;

;

GTT

LineSeprator

USA

;

25

;

6

;

<

;

12783

;

A

;

;

;

;

;

GTT

LineSeprator

Note: With Text files we must concern ourselves with the Record/Line(row) separator and the Field(column) Separator: They may vary. We must know about these.

Excel File

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

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

In Excel we do not have to concern ourselves with the row separator used internally by Excel ( vbCr & vbLf ), or the column Separator used internally by Excel ( vbTab ) : Excel does this for us. We do not need to add these when working with Excel Files. Internally, Excel uses those separators to make the cells that we see and work with.

_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )

Excel Files A B C D E F G H I J K

1 USA vbTab 101010 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT

vbCr & vbLf

2 USA vbTab 22 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT

vbCr & vbLf

3 USA vbTab 17388 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT

vbCr & vbLf

4 USA vbTab 100 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT

vbCr & vbLf

5 USA vbTab 25 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT

vbCr & vbLf

Worksheet: Sheet1

Note: In Excel we do not have to concern ourselves with the row seperator, vbCr & vbLf or the column Seperator, vbTab: Excel does this for us. We do not need to add these when working with Excel Files
We will only see this:

_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )

Excel Files A B C D E F G H I J K L

1 USA 101010 6 < 12783 A GTT

2 USA 22 6 < 12783 A GTT

3 USA 17388 6 < 12783 A GTT

4 USA 100 6 < 12783 A GTT

5 USA 25 6 < 12783 A GTT

6

Worksheet: Sheet1

Data File

( 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.

05-15-2020, 08:18 PM
DocAElstein

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
05-15-2020, 08:18 PM
DocAElstein

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
05-15-2020, 08:18 PM
DocAElstein

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

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

_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

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

I

J

K

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

05-18-2020, 06:20 PM
DocAElstein

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

_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

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

_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

_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

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

I

J

K

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

_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

_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

_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

Show 40 post(s) from this thread on one page