vba Copy Paste Conditional to put remark 1 2 3 .. etc

**fixer** · 05-22-2020, 02:07 PM

Moderstor notice:
Copy of a similar post in another forum, just for future referrence... ( Post made by Avinash in one of his many forum names ,
https://www.excelforum.com/excel-pro...een-files.html )

Hi there, I am looking for a macro As mentioned below
Plz see the sample file attached below
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: 1 20June excelforum.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Worksheet: 1-Sheet1 20 June excelforum

_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Symbol
2	ACC	22	1	2	3	4
3	ADANIENT	25	1	2	3
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
7	ASIANPAINT	236
8	AMBUJACEM	1270
9	APOLLOHOSP	157
10	APOLLOPIPE	14361
11	ASHOKLEY	212
12	AUROPHARMA	275
13	AXISBANK	5900
14	BAJAJ-AUTO	16669

Worksheet: Sheet1 20 June excelforum

Original question in this Thread: Post#1
https://excelfox.com/forum/showthrea...mark-1-2-3-etc
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B
If column I is buy then see the value of column K & if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
remark will be in series like 1,2,3,4,5,6 and so on
vba is palced in a separate file
all files are located in same place
and after putting the remark clear sheet 1 and sheet 2

Post #25:
https://excelfox.com/forum/showthrea...ll=1#post13161
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13161&viewfull=1#post13161
If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row

macro will be placed in 2.xlsm
all files re located in different path
sheet name can be anything

**DocAElstein** · 05-30-2020, 12:49 PM

_____ Workbook: 1 20June excelforum.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Worksheet: 1-Sheet1 20 June excelforum
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Symbol
2	ACC	22	1	2	3	4
3	ADANIENT	25	1	2	3
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
7	ASIANPAINT	236

Worksheet: Sheet1 20 June excelforum

Code:

Symbol												
ACC	        22	1	2	3	4							
ADANIENT	25	1	2	3								
ADANIPORTS	15083	1										
ADANIPOWER	17388	1	2	3	4	5	6	7				
AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
ASIANPAINT	236											
AMBUJACEM	1270											
APOLLOHOSP	157											
APOLLOPIPE	14361											
ASHOKLEY	212											
AUROPHARMA	275											
AXISBANK	5900											
BAJAJ-AUTO	16669											
BAJAJFINSV	16675											
BAJFINANCE	317											
BALKRISIND	335											
BANKBARODA	4668											
BATAINDIA	371											
BEL	        383											
BERGEPAINT	404											
BHARATFORG	422											
BHARTIARTL	10604											
BHEL	             438											
BIOCON	11373											
BOSCHLTD	2181											
BPCL	             526											
BRITANNIA	547											
CADILAHC	7929											
CANBK	    10794											
CASTROLIND	1250											
CENTURYTEX	625											
CESC	        628											
CHOLAFIN	685											
CIPLA	        694											
COALINDIA	20374											
COLPAL	15141											
CONCOR	4749											
CUMMINSIND	1901											
DABUR	772											
DIVISLAB	10940											
DLF	         14732											
DRREDDY	881											
EICHERMOT	910											
EQUITAS	16852											
ESCORTS	958											
EXIDEIND	676											
FEDERALBNK	1023											
GAIL	         4717											
GLENMARK	7406											
GMRINFRA	13528											
GODREJCP	10099											
GRASIM   	1232											
HAVELLS	9819											
HCLTECH	7229											
HDFC   	1330											
HDFCBANK	1333											
HEROMOTOCO	1348											
HINDALCO	1363											
HINDPETRO	1406											
HINDUNILVR	1394											
IBULHSGFIN	30125											
ICICIBANK	4963											
ICICIPRULI	18652											
IDEA	        14366											
IDFCFIRSTB	11184											
IGL	        11262											
INDIGO	11195											
INDUSINDBK	5258											
INFRATEL	29135											
INFY	       1594											
IOC	       1624											
ITC	       1660											
JINDALSTEL	6733											
JSWSTEEL	11723											
JUBLFOOD	18096											
JUSTDIAL	29962											
KOTAKBANK	1922											
L&TFH	     24948											
LICHSGFIN	1997											
LT	       11483											
LUPIN	10440											
M&M	        2031											
M&MFIN	13285											
MANAPPURAM	19061											
MARICO	4067											
MARUTI	10999											
MCDOWELL-N	10447											
MFSL	          2142											
MGL	          17534											
MINDTREE	14356											
MOTHERSUMI	4204											
MRF	           2277											
MUTHOOTFIN	23650											
NATIONALUM	6364											
NBCC	         31415											
NCC	         2319											
NESTLEIND	17963											
NIITTECH	11543											
NMDC	15332											
NTPC	        11630											
OIL	         17438											
ONGC	2475											
PAGEIND	14413											
PEL	            2412											
PETRONET	11351											
PFC	                14299											
PIDILITIND	2664											
PNB	                 10666											
POWERGRID	14977											
PVR	                     13147											
RAMCOCEM	2043											
RBLBANK	18391											
RECLTD	15355											
RELIANCE	2885											
SAIL	                     2963											
SBIN	                  3045											
SHREECEM	3103											
SIEMENS	3150											
SRF	                    3273											
SRTRANSFIN	4306											
SUNPHARMA	3351											
SUNTV	13404											
TATACHEM	3405											
TATAMOTORS	3456											
TATAMTRDVR	16965											
TATAPOWER	3426											
TATASTEEL	3499											
TCS	                  11536											
TECHM	13538											
TITAN	3506											
TORNTPHARM	3518											
TORNTPOWER	13786											
TVSMOTOR	8479											
UBL	                   16713											
UJJIVAN	17069											
ULTRACEMCO	11532											
UPL	                11287											
VEDL	                  3063											
VOLTAS	3718											
WIPRO	3787											
ZEEL	                 3812

**DocAElstein** · 05-30-2020, 01:30 PM

This and the next few posts are purely done out of interest looking at the alternative solutions supplied elsewhere for the similar question…. vba Copy Paste Conditional to put remark 1 2 3 .. etc…..

Here we analyse the solutions to this….

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

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Worksheet: 1-Sheet1 20 June excelforum

If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Symbol
2	ACC	22	1	2	3	4
3	ADANIENT	25	1	2	3
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
7	ASIANPAINT	236

Worksheet: Sheet1 20 June excelforum

Code:

Symbol												
ACC	        22	1	2	3	4							
ADANIENT	25	1	2	3								
ADANIPORTS	15083	1										
ADANIPOWER	17388	1	2	3	4	5	6	7				
AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
ASIANPAINT	236											
AMBUJACEM	1270											
APOLLOHOSP	157											
APOLLOPIPE	14361											
ASHOKLEY	212											
AUROPHARMA	275											
AXISBANK	5900											
BAJAJ-AUTO	16669											
BAJAJFINSV	16675											
BAJFINANCE	317											
BALKRISIND	335											
BANKBARODA	4668											
BATAINDIA	371											
BEL	        383											
BERGEPAINT	404											
BHARATFORG	422											
BHARTIARTL	10604											
BHEL	             438											
BIOCON	11373											
BOSCHLTD	2181											
BPCL	             526											
BRITANNIA	547											
CADILAHC	7929											
CANBK	    10794											
CASTROLIND	1250											
CENTURYTEX	625											
CESC	        628											
CHOLAFIN	685											
CIPLA	        694											
COALINDIA	20374											
COLPAL	15141											
CONCOR	4749											
CUMMINSIND	1901											
DABUR	772											
DIVISLAB	10940											
DLF	         14732											
DRREDDY	881											
EICHERMOT	910											
EQUITAS	16852											
ESCORTS	958											
EXIDEIND	676											
FEDERALBNK	1023											
GAIL	         4717											
GLENMARK	7406											
GMRINFRA	13528											
GODREJCP	10099											
GRASIM   	1232											
HAVELLS	9819											
HCLTECH	7229											
HDFC   	1330											
HDFCBANK	1333											
HEROMOTOCO	1348											
HINDALCO	1363											
HINDPETRO	1406											
HINDUNILVR	1394											
IBULHSGFIN	30125											
ICICIBANK	4963											
ICICIPRULI	18652											
IDEA	        14366											
IDFCFIRSTB	11184											
IGL	        11262											
INDIGO	11195											
INDUSINDBK	5258											
INFRATEL	29135											
INFY	       1594											
IOC	       1624											
ITC	       1660											
JINDALSTEL	6733											
JSWSTEEL	11723											
JUBLFOOD	18096											
JUSTDIAL	29962											
KOTAKBANK	1922											
L&TFH	     24948											
LICHSGFIN	1997											
LT	       11483											
LUPIN	10440											
M&M	        2031											
M&MFIN	13285											
MANAPPURAM	19061											
MARICO	4067											
MARUTI	10999											
MCDOWELL-N	10447											
MFSL	          2142											
MGL	          17534											
MINDTREE	14356											
MOTHERSUMI	4204											
MRF	           2277											
MUTHOOTFIN	23650											
NATIONALUM	6364											
NBCC	         31415											
NCC	         2319											
NESTLEIND	17963											
NIITTECH	11543											
NMDC	15332											
NTPC	        11630											
OIL	         17438											
ONGC	2475											
PAGEIND	14413											
PEL	            2412											
PETRONET	11351											
PFC	                14299											
PIDILITIND	2664											
PNB	                 10666											
POWERGRID	14977											
PVR	                     13147											
RAMCOCEM	2043											
RBLBANK	18391											
RECLTD	15355											
RELIANCE	2885											
SAIL	                     2963											
SBIN	                  3045											
SHREECEM	3103											
SIEMENS	3150											
SRF	                    3273											
SRTRANSFIN	4306											
SUNPHARMA	3351											
SUNTV	13404											
TATACHEM	3405											
TATAMOTORS	3456											
TATAMTRDVR	16965											
TATAPOWER	3426											
TATASTEEL	3499											
TCS	                  11536											
TECHM	13538											
TITAN	3506											
TORNTPHARM	3518											
TORNTPOWER	13786											
TVSMOTOR	8479											
UBL	                   16713											
UJJIVAN	17069											
ULTRACEMCO	11532											
UPL	                11287											
VEDL	                  3063											
VOLTAS	3718											
WIPRO	3787											
ZEEL	                 3812

**DocAElstein** · 05-31-2020, 12:56 PM

The solution from karmapala https://www.excelforum.com/excel-pro...ml#post5353174

Code:

 Sub karmapala()   '     https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174
Dim arr() As Variant

Set wb1 = Workbooks("1.xls")
Set sh1 = wb1.Sheets("1-Sheet1")
Set Rng = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(xlUp))
Set wb2 = Workbooks("Macro.xlsm")
Set sh2 = wb2.Sheets("Sheet1")
x = 0

For Each cell In Rng
    If cell.Value = cell.Offset(0, 1).Value And cell.Value <> cell.Offset(0, 2).Value Then
    ReDim Preserve arr(x)
    arr(x) = cell.Offset(0, 5)
    x = x + 1
    End If

    If cell.Value <> cell.Offset(0, 1) And cell.Value = cell.Offset(0, 2) Then
    ReDim Preserve arr(x)
    arr(x) = cell.Offset(0, 5)
    x = x + 1
    End If
Next

If x = 0 Then Exit Sub

For Each el In arr
Set c = sh2.Range("B:B").Find(el, lookat:=xlWhole)
    If Not c Is Nothing Then
    FirstAddress = c.Address
        Do
        If c.Offset(0, 1).Value = "" Then
        c.Offset(0, 1).Value = 1
        Else
        c.End(xlToRight).Offset(0, 1).Value = c.End(xlToRight).Value + 1
        End If
        Set c = sh2.Range("B:B").FindNext(c)
        Loop While c.Address <> FirstAddress
    End If
Next

End Sub

The logic of the macro from karmapala works on a logic suiting a slightly modified requirement description. It recognises that we have a pair of criteria resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm, after which the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

So in Rem 2 of my version of karmapala’s macro , a one dimensional array, arr() , is made to hold those values. At the end of that code section we have this arr():
arr() of column I meeting criteria.JPG : https://imgur.com/MhEsfNm

22

25

17388

100

or
arr()={22, 25, 17388, 100}

Rem 3 then uses the .Find ( and .Find Next ) method to find the mating row in macro.xlsm , and do the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

here the before
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	B	C	D	E	F	G	H	I	J	K	L	M
1
2	22	1	2	3	4
3	25	1	2	3
4	15083	1
5	17388	1	2	3	4	5	6	7
6	100	1	2	3	4	5	6	7	8	9	10	11
7	236

Worksheet: Sheet1 20 June excelforum

here the after
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	22	1	2	3	4	5
3	25	1	2	3	4
4	15083	1
5	17388	1	2	3	4	5	6	7	8
6	100	1	2	3	4	5	6	7	8	9	10	11	12
7	236

Worksheet: Sheet1 20 June excelforum

here is my version of the macro: https://excelfox.com/forum/showthrea...ll=1#post14142

**DocAElstein** · 06-01-2020, 02:32 PM

The formula solution from pharmacologist ( https://www.excelforum.com/excel-pro...ml#post5353103 )

Originally Posted by pharmacologist

Put this in the top of column I of 1.xls and drag down and across however many columns you need.

Code:

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"",INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"")

This is a nice idea, but there are a few problems.

Two minor problems:
_(i) A simple problem possibly a typo : you should not use column I of 1.xls , since that has data in. We can use any column away from the data, or we could start the formula at some convenient row down away from the data

_(ii) The formula will not work in 1.xls , since it will try to reference all the rows in a column in macro.xlsm – there are 1048576 rows in macro.xlsm, but a formula used in 1.xls will give us a problem , saying that it cannot use more than the row number for pre Excel 2007, which is 65536

Before looking further into the formula, lets simplifier/shorten/change a few names and paths to make the analysis and modification of the formula easier to follow.
I will store my files on my desktop. So the full path and file name of my files are:
C:\Users\Elston\Desktop\macro.xlsm
C:\Users\Elston\Desktop\1.xls
I will use a sheet name to help me later navigating the many duplicated / similar / cross posted questions fro the OP , Avinash. I will use
1_xlsSh1efJ20
macro_xlsmSh1efJ20 ( This is just for me to reference the workbooks as that uploaded at excelforum on June 20 ( https://www.excelforum.com/excel-pro...ml#post5352953 )
So before the use of any formula or coding, this is the situation:
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Symbol
2	ACC	22	1	2	3	4
3	ADANIENT	25	1	2	3
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11
7	ASIANPAINT	236

Worksheet: macro_xlsmSh1efJ20

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

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Worksheet: 1-Sheet1 20 June excelforum

My version of pharmacologist’s formula to suit the workbook and worksheets name is then initially:

Code:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"") 

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"",INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"")

Note: I have given two forms. 1 is in English Excel, the other is in my German Excel , which I have change the formula default option to English from thee default German, but which still uses the ; separator rather than the English conventional ,
If I try to use this formula, in , for example, cell K2 of 1.xls , then I get the error situation mentioned in (i) associated with the row and column count:
RowsCountProblemInFormula.JPG : https://imgur.com/KZtIJ3h

In the next posts I look at corrections/ adjustments to the formula. ( Note the output required by the OP , is that produced by the working solution from ( https://excelfox.com/forum/showthrea...ll=1#post14130
https://excelfox.com/forum/showthrea...ll=1#post14142 )
This is the solution required

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Symbol
2	ACC	22	1	2	3	4	5
3	ADANIENT	25	1	2	3	4
4	ADANIPORTS	15083	1
5	ADANIPOWER	17388	1	2	3	4	5	6	7	8
6	AMARAJABAT	100	1	2	3	4	5	6	7	8	9	10	11	12
7	ASIANPAINT	236

Worksheet: macro_xlsmSh1efJ20

**DocAElstein** · 06-07-2020, 09:07 PM

row problem , (ii)
We can overcome the row problem , (ii) , by fixing the ranges used in some parts of the formula. In the practice we might know the maximum rows, or we could estimate the largest most likely. ( If we were using VBA to put the formula in, then we could dynamically determine it ( http://www.excelfox.com/forum/showth...ll=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) )
For simplicity of this analysis we will limit the rows to rows 2 – 7
This formula example solves the row problem , (ii)

Code:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

Further adjustments/Corrections
The OP actually wants the results in macro.xlsm, not 1.xls. ( As the OP did not produce a before and after, it is easy to get confused…. – half the time, this OP, Avinash doesn’t know himself what he wants )
The formula is already quite flexible in that it can be placed in any cell in 1.xls. We can make it further flexible by changing the cell references for cells in 1.xls to include the workbook and first worksheet of 1.xls, ( '[1.xls]1_xlsSh1efJ20'! ) This will then make the formula work equally well in either 1.xls or macro.xlsm

Code:

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

For example, I put this formula in R2 of macro.xlsm and drag it across and down. Here for example just shown 4 of the formulas: ( Note Excel has removed the workbook and worksheet referrence for 1.xls - this is just a strange habit of Excel : - Excel removes the bits of the cell referrence that it does not need ... )
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	R	S
2	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")
3	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

Worksheet: macro_xlsmSh1efJ20

I get for the full range giving the following results.

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

_Row\^Col	R	S	T	U	V	W	X	Y	Z	AA	AB
2	1	2	3	4
3	1	2	3
4
5	1	2	3	4	5	6	7
6	1	2	3	4	5	6	7	8	9	10	11
7

Worksheet: macro_xlsmSh1efJ20

So it looks like the formula is “working” … but it is not giving us the correct results :
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

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

Worksheet: macro_xlsmSh1efJ20

We will investigate further in the next posts

**DocAElstein** · 06-08-2020, 08:39 PM

General formula construction / relation to OP requirement
It is helpful at this stage to make an initial attempt understand the formula.

We can see that we have an “outer” If , pseudo like

If ( criteria met resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm ,
____Then do some stuff to get the values 1 , 2 , 3 … etc ,
_______Else "" )

Here we can see the cause of the missing 1 in row 4 . This is the situation where the original data, unchanged should be present. So replacing "" with a reference to the original data , [macro.xlsm]macro_xlsmSh1efJ20!C2, should overcome this problem.
However, we will see a small problem that across the row we will have 0s instead of nothing, since , by default, Excel is evaluating like “nothing” number, which is zero
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	R	S	T	U
4	1	0	0	0

Worksheet: macro_xlsmSh1efJ20

This can be overcome with a trick to make it show “nothing text” by adding a &"" to the cell value, [macro.xlsm]macro_xlsmSh1efJ20!C2&""

So finally we have

Code:

 =IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));[macro.xlsm]macro_xlsmSh1efJ20!C2&"")

We have now solved the problem to put data in unchanged for the unmatched case. We need to modify somehow the first part of the outer If to get the correct results in.
We need to have values of 1 greater than the found values , and offset by one to the right

This part of the formula would normally, for the first cell return us the value of 1
INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))
This part actually is present twice in an If, pseudo
If ( This is blank , then "" , else This )

I can see a way to do this to get the results for all but the first column, as follows: We would look for a match in the previous column and the result would be added by 1

For example, this formula…_

Code:

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1);C2&"")

_.. almost gives us what we want, for example in the second row
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	R	S	T	U	V	W
2	23	2	3	4	5

Worksheet: macro_xlsmSh1efJ20

One way to get the first column correct, could be to have a check on the part giving the result to see if the column first column is under consideration , in which case , we then need again to see if it is empty ( If(C2="";"";1) ) in which case a "" will be returned, else a 1

If(Column(C2)=3;If(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)

Finally, a working formula seems to be ( For in , for example R2 and copied across and down ) ,
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col

R

2

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")

Worksheet: macro_xlsmSh1efJ20

**DocAElstein** · 06-08-2020, 08:39 PM

Some other final notes to formula solution

The formula from pharmacologist seems to be matching columns A in macro.xlsm to B in 1.xls. The OP wanted columns B in macro.xlsm to I in 1.xls. But I will ignore that for now.

The final formula version of mine will also work if the workbook, 1.xls , is closed.
To verify and get the formula for that, simply close 1.xls and observe the formula changed automatically by Excel

Code:

 =IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2);AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";IF(COLUMN(C2)=3;IF(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1));C2&"")

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

_Row\^Col	R	S
2	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D2)=3,IF(D2="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D2&"")
3	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C3)=3,IF(C3="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C3&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D3)=3,IF(D3="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D3&"")

Worksheet: macro_xlsmSh1efJ20

macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk

**DocAElstein** · 06-10-2020, 02:09 PM

Spare post for later use

**DocAElstein** · 06-10-2020, 02:09 PM

The next version.....

Code:

Sub test() '  https://www.excelforum.com/excel-programming-vba-macros/1321581-put-data-in-series-by-vba.html#post5363303
    Dim r As Range, x, BK As String, myVal
    BK = "'" & ThisWorkbook.Path & "\[1(sample).xls]1-Sheet1'!"
    With Sheets("sheet1")
        For Each r In .Range("b2", .Range("b" & Rows.Count).End(xlUp))
            myVal = r.Value
            If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
            x = ExecuteExcel4Macro("match(" & r.Value & "," & BK & "r1c9:r10000c9,0)")
            If IsNumeric(x) Then
                With .Cells(r.Row, Columns.Count).End(xlToLeft)
                    .Cells(1, 2) = .Value + 1
                End With
            End If
        Next
    End With
End Sub