Formula listing items from several columns in one column

lechev65

Board Regular
Joined
Mar 7, 2010
Messages
147
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.
A</SPAN>
B</SPAN>
C</SPAN>
D</SPAN>
E</SPAN>
1</SPAN>
U </SPAN>
U </SPAN>
Y </SPAN>
Z</SPAN>
W</SPAN>
2</SPAN>
k</SPAN>
k</SPAN>
c</SPAN>
o</SPAN>
m</SPAN>
3</SPAN>
i</SPAN>
i</SPAN>
s</SPAN>
j</SPAN>
b</SPAN>
4</SPAN>
r</SPAN>
r</SPAN>
h</SPAN>
r</SPAN>
a</SPAN>
5</SPAN>
g</SPAN>
g</SPAN>
u</SPAN>
q</SPAN>
6</SPAN>
v</SPAN>
v</SPAN>
f</SPAN>
c</SPAN>
7</SPAN>
Y </SPAN>
r</SPAN>
8</SPAN>
c</SPAN>
z</SPAN>
9</SPAN>
s</SPAN>
10</SPAN>
h</SPAN>
11</SPAN>
Z</SPAN>
12</SPAN>
o</SPAN>
13</SPAN>
j</SPAN>
14</SPAN>
r</SPAN>
15</SPAN>
u</SPAN>
16</SPAN>
f</SPAN>
17</SPAN>
r</SPAN>
18</SPAN>
z</SPAN>
19</SPAN>
W</SPAN>
20</SPAN>
m</SPAN>
21</SPAN>
b</SPAN>
22</SPAN>
a</SPAN>
23</SPAN>
q</SPAN>
24




</SPAN>
c



</SPAN>

<TBODY>
</TBODY>

Thank you in advance!
Krasimir
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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>

Sheet1</SPAN>

A</SPAN>
B</SPAN>
C</SPAN>
D</SPAN>
E</SPAN>
F</SPAN>
G</SPAN>
H</SPAN>
J</SPAN>
1</SPAN>
U</SPAN>
U</SPAN>
S</SPAN>
B</SPAN>
T</SPAN>
L</SPAN>



2</SPAN>
uvu</SPAN>
uvu</SPAN>
ab23</SPAN>
sdf</SPAN>
qwe</SPAN>
por</SPAN>



3</SPAN>
vuv</SPAN>
vuv</SPAN>
nmn</SPAN>
we</SPAN>

987c</SPAN>



4</SPAN>
uvu</SPAN>
uvu</SPAN>

sf</SPAN>

por</SPAN>



5</SPAN>
vuv</SPAN>
vuv</SPAN>

4689</SPAN>





6</SPAN>
345</SPAN>
345</SPAN>







7</SPAN>
S</SPAN>








8</SPAN>
ab23</SPAN>








9</SPAN>
nmn</SPAN>








10</SPAN>
B</SPAN>








11</SPAN>
sdf</SPAN>








12</SPAN>
we</SPAN>








13</SPAN>
sf</SPAN>








14</SPAN>
4689</SPAN>








15</SPAN>
T</SPAN>








16</SPAN>
qwe</SPAN>








17</SPAN>
L</SPAN>








18</SPAN>
por</SPAN>








19</SPAN>
987c</SPAN>








20</SPAN>
por</SPAN>









Sheet2</SPAN>

A</SPAN>
B</SPAN>
C</SPAN>
D</SPAN>
E</SPAN>
F</SPAN>
G</SPAN>
H</SPAN>
J</SPAN>
1</SPAN>
U</SPAN>
N</SPAN>
W</SPAN>
S</SPAN>
Y</SPAN>
T</SPAN>
B</SPAN>
L</SPAN>
P</SPAN>
2</SPAN>
uvu</SPAN>
ab</SPAN>
cdc</SPAN>
ab23</SPAN>
ded</SPAN>
qwe</SPAN>
sdf</SPAN>
por</SPAN>
sd</SPAN>
3</SPAN>
vuv</SPAN>
ba</SPAN>
dcd</SPAN>
nmn</SPAN>
ede</SPAN>

we</SPAN>
987c</SPAN>
asd</SPAN>
4</SPAN>
uvu</SPAN>
ab</SPAN>


ded</SPAN>

sf</SPAN>
por</SPAN>
ad</SPAN>
5</SPAN>
vuv</SPAN>
ba</SPAN>


ede</SPAN>

4689</SPAN>

asd</SPAN>
6</SPAN>
345</SPAN>
ab</SPAN>


ded</SPAN>



asda</SPAN>
7</SPAN>

ba</SPAN>






asd</SPAN>
8</SPAN>

ab</SPAN>






asd</SPAN>
9</SPAN>

ba</SPAN>







10</SPAN>

ab</SPAN>








<TBODY>
</TBODY>

Thank you again in advance!</SPAN>
Krasimir</SPAN>
 
Upvote 0
Sorry - not sure I understand. Why can't you just make the formula reference Sheet2 instead?

Regards
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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

A
B
C
D
E
F
G
H
1
LLLL
LLLL
MMMM
YYYY
ZZZZ
WWWW
RRRR
OOO
2
3322
3322
gjh
m
n
4
2w3e
o
3
d
d
vbg
23
v
klj
9i9o
Q
4
6g
6g
k12kl
m
n


o
5
3h
3h
ert
ghh
3214


5
6
MMMM

ghj

n



7
gjh



223



8
vbg



n



9
k12kl







10
ert







11
ghj







12
YYYY







13
m







14
23







15
m







16
ghh







17
ZZZZ







18
n







19
v







20
n







21
3214







22
n







23
223







24
n







25
WWWW







26
4







27
klj







28
RRRR







29
2w3e







30
9i9o







31
OOO







32
o







33
Q







34
o







35
5








<tbody>
</tbody>

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

A
B
C
D
E
F
G
H
1
LLLL
LLLL
YYYY
OOO
ZZZZ



2
3322
3322
m
o
n
#N/A
#N/A
#N/A
3
d
d
23
Q
v



4
6g
6g
m
o
n



5
3h
3h
ghh
5
3214



6




n



7




223



8




n



9








10








11








12








13








14








15








16








17








18








19








20








21








22








23








24








25








26








27








28








29








30








31








32








33








34








35









<tbody>
</tbody>

Sheet2

A
B
C
D
E
F
G
H
1
LLLL
MMMM
YYYY
ZZZZ
WWWW
RRRR
OOO

2
3322
gjh
m
n
4
2w3e
o

3
d
vbg
23
v
klj
9i9o
Q

4
6g
k12kl
m
n


o

5
3h
ert
ghh
3214


5

6

ghj

n




7



223




8



n




9








10








11








12








13








14








15








16








17








18








19








20








21








22








23








24








25








26








27








28








29








30








31








32








33








34








35









<tbody>
</tbody>

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

Kind Regards,
Krasimir
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top