Formula listing items from several columns in one column

[lechev65]

Hi Mr Excel,

In columns B, C, D & E I have titles with items. I need formula listing the titles (upper case letters) with different items (lower case letters) in column A as shown on the example below.
The number of the items is variable linked to database.
[TABLE="width: 364"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]U </SPAN>
[/TD]
[TD]U </SPAN>
[/TD]
[TD]Y </SPAN>
[/TD]
[TD]Z</SPAN>
[/TD]
[TD]W</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]k</SPAN>
[/TD]
[TD]k</SPAN>
[/TD]
[TD]c</SPAN>
[/TD]
[TD]o</SPAN>
[/TD]
[TD]m</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]i</SPAN>
[/TD]
[TD]i</SPAN>
[/TD]
[TD]s</SPAN>
[/TD]
[TD]j</SPAN>
[/TD]
[TD]b</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]r</SPAN>
[/TD]
[TD]r</SPAN>
[/TD]
[TD]h</SPAN>
[/TD]
[TD]r</SPAN>
[/TD]
[TD]a</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]g</SPAN>
[/TD]
[TD]g</SPAN>
[/TD]
[TD][/TD]
[TD]u</SPAN>
[/TD]
[TD]q</SPAN>
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]v</SPAN>
[/TD]
[TD]v</SPAN>
[/TD]
[TD][/TD]
[TD]f</SPAN>
[/TD]
[TD]c</SPAN>
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Y </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]r</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]c</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]z</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]s</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]h</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]Z</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]o</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD]j</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]r</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]u</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]f</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD]r</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD]z</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19</SPAN>
[/TD]
[TD]W</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20</SPAN>
[/TD]
[TD]m</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21</SPAN>
[/TD]
[TD]b</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22</SPAN>
[/TD]
[TD]a</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23</SPAN>
[/TD]
[TD]q</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24




</SPAN>
[/TD]
[TD]c



</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Thank you in advance!
Krasimir

 

[XOR LX]

Hi,

Assuming your data is in B1:E10, this array formula** in A1:

=IF(ROWS($1:1)>COUNTIF($B$1:$E$10,"*"),"",INDIRECT(TEXT(SUM(10^{3,0}*SUBSTITUTE(MID(SMALL(IF(LEN($B$1:$E$10)>0,COLUMN($B$1:$E$10)*10^6+ROW($B$1:$E$10)),ROWS($1:1)),{5,1},3),"0","")),"R000C000"),0))

Copy down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[lechev65]

Hi XOR LX,

Thank you very much for the prompt reply!

I have tried the formula and it works perfectly applied for the above example table.

Actually the columns are 150 (from B to EU) with 10 to 200 items in each column and

the total number of the items listed in column A could be more than 20000~30000.

Could you please advise how to modify the formula?

Thank you in advance!

Kind regards,

Krasimir

 

[XOR LX]

Hi,

Slightly different approach (the above would fail with row numbers containing a zero):

Assuming a data range of B1:EU200, and also that the blanks, if any, at the end of each column are "genuine" blanks (and not, for example, the "" as the result of e.g. formulas in those cells), then go to Name Manager and define a new name, Row_Array say, as:

=ROW(INDIRECT("1:"&COLUMNS($B$1:$EU$200)))

Your (array) formula in A1 is then:

=IF(ROWS($1:1)>COUNTA($B$1:$EU$200),"",INDEX(INDEX($B$1:$EU$200,,MATCH(TRUE,MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),SUBTOTAL(3,OFFSET($B$1:$B$200,,Row_Array-1,,)))>=ROWS($1:1),0)),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),SUBTOTAL(3,OFFSET($B$1:$B$200,,Row_Array-1,,)))),ROWS($1:1))))

Again, copy down until you start to get blanks for the results.

Regards

 

[lechev65]

Dear</SPAN></SPAN>
The formula is amazing and made me very happy! </SPAN>Thank you very very much again!</SPAN>
Is that rude or too much if I ask you for one more formula?</SPAN>
The formula you have advised is in Sheet1 in A1:A3000 and lists the items in columns from B to EU.</SPAN>
My database is in Sheet2 in columns A to ZZ with approximately 10 to 200 items in each column.</SPAN>
Every time I have to copy and paste the data from Sheet2 to Sheet1 depending on the needs … </SPAN>
Could you please advise a formula in Sheet1 columns B to EU, matching the criteria manually input </SPAN>
in B1,C1,D1,E1,F1,…EU1 and returning the values from the respective columns in Sheet2 as shown below?</SPAN>

[TABLE="width: 503"]
<TBODY>[TR]
[TD="colspan: 10"]Sheet1</SPAN>
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]S</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]ab23</SPAN>
[/TD]
[TD]sdf</SPAN>
[/TD]
[TD]qwe</SPAN>
[/TD]
[TD]por</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]nmn</SPAN>
[/TD]
[TD]we</SPAN>
[/TD]
[TD]
[/TD]
[TD]987c</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]
[/TD]
[TD]sf</SPAN>
[/TD]
[TD]
[/TD]
[TD]por</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]
[/TD]
[TD]4689</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]345</SPAN>
[/TD]
[TD]345</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]S</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]ab23</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]nmn</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]sdf</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]we</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD]sf</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]4689</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]qwe</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD]por</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19</SPAN>
[/TD]
[TD]987c</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20</SPAN>
[/TD]
[TD]por</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="colspan: 10"]
[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Sheet2</SPAN>
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]W</SPAN>
[/TD]
[TD]S</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]ab</SPAN>
[/TD]
[TD]cdc</SPAN>
[/TD]
[TD]ab23</SPAN>
[/TD]
[TD]ded</SPAN>
[/TD]
[TD]qwe</SPAN>
[/TD]
[TD]sdf</SPAN>
[/TD]
[TD]por</SPAN>
[/TD]
[TD]sd</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]ba</SPAN>
[/TD]
[TD]dcd</SPAN>
[/TD]
[TD]nmn</SPAN>
[/TD]
[TD]ede</SPAN>
[/TD]
[TD]
[/TD]
[TD]we</SPAN>
[/TD]
[TD]987c</SPAN>
[/TD]
[TD]asd</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]uvu</SPAN>
[/TD]
[TD]ab</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]ded</SPAN>
[/TD]
[TD]
[/TD]
[TD]sf</SPAN>
[/TD]
[TD]por</SPAN>
[/TD]
[TD]ad</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]vuv</SPAN>
[/TD]
[TD]ba</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]ede</SPAN>
[/TD]
[TD]
[/TD]
[TD]4689</SPAN>
[/TD]
[TD]
[/TD]
[TD]asd</SPAN>
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]345</SPAN>
[/TD]
[TD]ab</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]ded</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]asda</SPAN>
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]
[/TD]
[TD]ba</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]asd</SPAN>
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]
[/TD]
[TD]ab</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]asd</SPAN>
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]
[/TD]
[TD]ba</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]
[/TD]
[TD]ab</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

Thank you again in advance!</SPAN>
Krasimir</SPAN>

 

[XOR LX]

Sorry - not sure I understand. Why can't you just make the formula reference Sheet2 instead?

Regards

 

[lechev65]

Hi,
Because in Sheet1, column A, I need the items taken only from selected columns.
Sheet2 is my database with all the items.
I do not know how to make formula reference Sheet2 which will list the items from the
colums I need. Please also note that every time I need to make a list of the items from
different columns depending on the needs.

Kind Regards,
Krasimir

 

[XOR LX]

Not sure of your actual range references, but seems like you'd need something like this in B2 of Sheet1:

=IF(ROWS($1:1)>COUNTA(INDEX(Sheet2!$A$2:$ZZ$200,,MATCH(Sheet1!B$1,Sheet2!$A$1:$ZZ$1,0))),"",INDEX(Sheet2!$A$2:$ZZ$200,ROWS($1:1),MATCH(Sheet1!B$1,Sheet2!$A$1:$ZZ$1,0)))

Copy across as required and then down until you start to get blanks.

Regards

 

[lechev65]

Hi,
Thank you very much. I do really appreciate your commitment!

The last formula also works well and returns the items to the respective column matching the criteria but somehow interfere the formula in column A!? Please find here below details.

CASE I
New name defined “Row_Array” as:
=ROW(INDIRECT("1:"&COLUMNS($B$1:$EU$200)))
Array formula input in A1:A35
=IF(ROWS($1:1)>COUNTA($B$1:$EU$200),"",INDEX(INDEX($B$1:$EU$200,,MATCH(TRUE,MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),SUBTOTAL(3,OFFSET($B$1:$B$200,,Row_Array-1,,)))>=ROWS($1:1),0)),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),SUBTOTAL(3,OFFSET($B$1:$B$200,,Row_Array-1,,)))),ROWS($1:1))))

Manually input data in columns B to H

Result:

Sheet1
[TABLE="width: 541"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]LLLL
[/TD]
[TD]LLLL
[/TD]
[TD]MMMM
[/TD]
[TD]YYYY
[/TD]
[TD]ZZZZ
[/TD]
[TD]WWWW
[/TD]
[TD]RRRR
[/TD]
[TD]OOO
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3322
[/TD]
[TD]3322
[/TD]
[TD]gjh
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]4
[/TD]
[TD]2w3e
[/TD]
[TD]o
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]d
[/TD]
[TD]d
[/TD]
[TD]vbg
[/TD]
[TD]23
[/TD]
[TD]v
[/TD]
[TD]klj
[/TD]
[TD]9i9o
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6g
[/TD]
[TD]6g
[/TD]
[TD]k12kl
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]o
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3h
[/TD]
[TD]3h
[/TD]
[TD]ert
[/TD]
[TD]ghh
[/TD]
[TD]3214
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]MMMM
[/TD]
[TD]
[/TD]
[TD]ghj
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]gjh
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]223
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]vbg
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]k12kl
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]ert
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]ghj
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]YYYY
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]m
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]23
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]m
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]ghh
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]ZZZZ
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]v
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]3214
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]223
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]WWWW
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]klj
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]RRRR
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]2w3e
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]9i9o
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]OOO
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]o
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]Q
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]o
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 - blank

CASE II

Columns B to H from Sheet1 copied in columns A to G oh Sheet2.

The below formula input in Sheet1 B2:H35

=IF(ROWS($1:1)>COUNTA(INDEX(Sheet2!$A$2:$ZZ$200,,MATCH(Sheet1!B$1,Sheet2!$A$1:$ZZ$1,0))),"",INDEX(Sheet2!$A$2:$ZZ$200,ROWS($1:1),MATCH(Sheet1!B$1,Sheet2!$A$1:$ZZ$1,0)))

Result: The formula in column A returns the data only from column B … The formulas in the othe columns works properly returning the data from Sheet2.

Sheet1
[TABLE="width: 541"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]LLLL
[/TD]
[TD]LLLL
[/TD]
[TD]YYYY
[/TD]
[TD]OOO
[/TD]
[TD]ZZZZ
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3322
[/TD]
[TD]3322
[/TD]
[TD]m
[/TD]
[TD]o
[/TD]
[TD]n
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]d
[/TD]
[TD]d
[/TD]
[TD]23
[/TD]
[TD]Q
[/TD]
[TD]v
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6g
[/TD]
[TD]6g
[/TD]
[TD]m
[/TD]
[TD]o
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3h
[/TD]
[TD]3h
[/TD]
[TD]ghh
[/TD]
[TD]5
[/TD]
[TD]3214
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]223
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 541"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]LLLL
[/TD]
[TD]MMMM
[/TD]
[TD]YYYY
[/TD]
[TD]ZZZZ
[/TD]
[TD]WWWW
[/TD]
[TD]RRRR
[/TD]
[TD]OOO
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3322
[/TD]
[TD]gjh
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]4
[/TD]
[TD]2w3e
[/TD]
[TD]o
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]d
[/TD]
[TD]vbg
[/TD]
[TD]23
[/TD]
[TD]v
[/TD]
[TD]klj
[/TD]
[TD]9i9o
[/TD]
[TD]Q
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6g
[/TD]
[TD]k12kl
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]o
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3h
[/TD]
[TD]ert
[/TD]
[TD]ghh
[/TD]
[TD]3214
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[/TD]
[TD]ghj
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]223
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]n
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

I have checked everything many times but the result is the same: the formula in Sheet1 column A malfunction.

Kind Regards,
Krasimir

 

[XOR LX]

Hi,

Assuming a data range of B1:EU200, and also that the blanks, if any, at the end of each column are "genuine" blanks (and not, for example, the "" as the result of e.g. formulas in those cells),...

Yes, apologies. I'm contradicting my own earlier statement now, since this is precisely what you will have in some of these cells now.

Bear with me.

Regards