Formula listing items from several columns in one column

lechev65 · Mar 25, 2014

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>

Thank you in advance!
Krasimir

XOR LX · Mar 26, 2014

Try this amendment to the (array) formula in Sheet1 A1:

=IF(ROWS($1:1)>SUM(0+($B$1:$EU$200<>"")),"",INDEX(INDEX($B$1:$EU$200,,MATCH(TRUE,MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),TRANSPOSE(MMULT({1,1},COUNTIF(OFFSET($B$1:$B$200,,TRANSPOSE(Row_Array-1),,),{"?*";">0"}))))>=ROWS($1:1),0)),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT(--(Row_Array>=TRANSPOSE(Row_Array)),TRANSPOSE(MMULT({1,1},COUNTIF(OFFSET($B$1:$B$200,,TRANSPOSE(Row_Array-1),,),{"?*";">0"}))))),ROWS($1:1))))

Regards

lechev65 · Mar 26, 2014

Hi XOR,

Is there another solution or way of calculation?

Kind Regards,

Krasimir

lechev65 · Mar 26, 2014

Sorry, I miss to mention that the formula doesn't work and returns "#N/A" in each cell in the column A.

lechev65 · Mar 27, 2014

Hi XOR,

It works on Excel 2010 but on 2007 does not.

Please disregard the last two posts!

Thank you very much for your passion and understanding!

Kind Regards,

Krasimir

XOR LX · Mar 27, 2014

Interesting. I wasn't aware that there was a difference in the size limitation for array returns from MMULT between 2007 and 2010 (assuming that's where the issue lies). 2003, yes, but 2007?

Anyway, glad you got it to work. And you're welcome!

Regards

lechev65 · Mar 28, 2014

Hi XOR
My items in the database have codes which I deleted. After I did the work I found out that there are items with same name and very similar but different code. There are also many items without codes and this was the reason I ignored the codes in order to simplify the job but actually this was a big mistake. 
If the database (Sheet2) is arranged as shown here below, is it difficult to modify the two formulas in Sheet1 in order to get the result shown in columns A & B of Sheet1? Each two columns in Sheet2 have the same name in the two top merged cells.

Sheet2 

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>	J</SPAN>
1</SPAN>	LLLL</SPAN>	MMMM</SPAN>	YYYY</SPAN>	ZZZZ</SPAN>	WWWW</SPAN>
2</SPAN>	123BRK</SPAN>	aaaa</SPAN>	56TRN</SPAN>	ggjh</SPAN>	9876</SPAN>	mnmn</SPAN>	5432</SPAN>	ndnd</SPAN>	2468</SPAN>	4</SPAN>
3</SPAN>	12MNG</SPAN>	dddd</SPAN>	45LBR</SPAN>	ertt</SPAN>	13GBR</SPAN>	23qwq</SPAN>		vvrvr</SPAN>		klj</SPAN>
4</SPAN>	12KRC</SPAN>	dddd</SPAN>		k12kl</SPAN>		msms</SPAN>	23MNV</SPAN>	nnnn</SPAN>
5</SPAN>	1237</SPAN>	3hhh</SPAN>	45SNG</SPAN>	ertt</SPAN>	3451</SPAN>	ghhg</SPAN>		3214</SPAN>
6</SPAN>				ghjh</SPAN>			23NRT</SPAN>	nnnn</SPAN>
7</SPAN>								223m</SPAN>
8</SPAN>								frfd</SPAN>
9</SPAN>
10</SPAN>
11</SPAN>
12</SPAN>
13</SPAN>
14</SPAN>
15</SPAN>
16</SPAN>

Sheet1

A B C D E F G H I J
1 MMMM MMMM WWWW LLLL
2 56TRN ggjh 56TRN ggjh 2468 4 123BRK aaaa
3 45LBR ertt 45LBR ertt klj 12MNG dddd
4 k12kl k12kl 12KRC dddd
5 45SNG ertt 45SNG ertt 1237 3hhh
6 ghjh ghjh
7 WWWW
8 2468 4
9 klj
10 LLLL
11 123BRK aaaa
12 12MNG dddd
13 12KRC dddd
14 1237 3hhh
15
16

<TBODY>
</TBODY><COLGROUP><COL><COL span=10></COLGROUP>

Thank you again in advance!
Krasimir

XOR LX · Mar 28, 2014

Sorry - not sure I understand what you're asking. Are you saying the headers in Sheet2 are now merged, A1:B1, C1:D1, etc.? So what do you want in Sheet1 - merged headers as well? Not sure I can do that with a formula. Strongly suggest you unmerge those headers.

And also that there are now blanks in between the entries in Sheet2? And you don't want these blanks to appear in Sheet1?

Regards

lechev65 · Mar 28, 2014

Hi XOR,
I am sorry for my poor explanation.
In Sheet2 (database), I have columns with listed items. Each two columns (A & B, C & D, E & F, etc.) make one list of items where the first columns (A, C, E, G, I, etc.) is the code of the item and the second columns (B, D, F, H, J, etc.) is the item description. Some of the items have same descriptions but different codes. Some of them do not have codes. A1 & B1, C1 & D1, E1 & F1, etc. are not merged but there is nested the title of each list.
EXAMPLE
Sheet2

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>	J</SPAN>
1</SPAN>	LLLLLLLLLLLLLLLLLLLL</SPAN></SPAN>	MMMMMMMMMM</SPAN></SPAN>	YYYYYYYYYYYYYYYY</SPAN></SPAN>	ZZZZZZZZZZZZZZZZZ</SPAN></SPAN>	WWWWWWWWW</SPAN></SPAN>
2</SPAN>	123BRK</SPAN></SPAN>	aaaa</SPAN></SPAN>	56TRN</SPAN></SPAN>	ggjh</SPAN></SPAN>	9876</SPAN></SPAN>	mnmn</SPAN></SPAN>	5432</SPAN></SPAN>	ndnd</SPAN></SPAN>	2468</SPAN></SPAN>	4</SPAN></SPAN>
3</SPAN>	12MNG</SPAN></SPAN>	dddd</SPAN></SPAN>	45LBR</SPAN></SPAN>	ertt</SPAN></SPAN>	13GBR</SPAN></SPAN>	23qwq</SPAN></SPAN>		vvrvr</SPAN></SPAN>		klj</SPAN></SPAN>
4</SPAN>	12KRC</SPAN></SPAN>	dddd</SPAN></SPAN>		k12kl</SPAN></SPAN>		msms</SPAN></SPAN>	23MNV</SPAN></SPAN>	nnnn</SPAN></SPAN>
5</SPAN>	1237</SPAN></SPAN>	3hhh</SPAN></SPAN>	45SNG</SPAN></SPAN>	ertt</SPAN></SPAN>	3451</SPAN></SPAN>	ghhg</SPAN></SPAN>		3214</SPAN></SPAN>
6</SPAN>				ghjh</SPAN></SPAN>			23NRT</SPAN></SPAN>	nnnn</SPAN></SPAN>
7</SPAN>								223m</SPAN></SPAN>
8</SPAN>								frfd</SPAN></SPAN>
9</SPAN>
10</SPAN>
11</SPAN>
12</SPAN>
13</SPAN>
14</SPAN>
15</SPAN>

In Sheet1, columns (C & D, E & F, G & H, I & J, etc., where C1 & D1, E1 & F1, G1 & H1, I1 & J1, etc. are not merged) when I enter selected by me titles of the lists (criteria) the formula has to return below each title the respective items with their codes.
In Sheet1, in columns A & B, I need formula to list down the selected by me lists (Sheet1, columns C & D, E & F, G & H, I & J, etc.,) with their titles , codes and item description. If there is no code the cell should just remain blank, i.e the returned lists have to be exactly the same as the lists in database. Here below please find example how Sheet1 should look.

Example

List

LLLLLLLLLLLLLLLLLLLL - TITLE</SPAN></SPAN>
123BRK</SPAN></SPAN>	aaaa</SPAN></SPAN>
12MNG</SPAN></SPAN>	dddd</SPAN></SPAN>
12KRC</SPAN></SPAN>	dddd</SPAN></SPAN>
1237</SPAN></SPAN>	3hhh</SPAN></SPAN>
code</SPAN>	item description</SPAN>

Example:
Sheet1

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>	J</SPAN>
1</SPAN>	MMMMMMMMMM</SPAN></SPAN>	MMMMMMMMMM</SPAN></SPAN>	WWWWWWWWW</SPAN></SPAN>	LLLLLLLLLLLLLLLLLLLL</SPAN></SPAN>
2</SPAN>	56TRN</SPAN></SPAN>	ggjh</SPAN></SPAN>	56TRN</SPAN></SPAN>	ggjh</SPAN></SPAN>	2468</SPAN></SPAN>	4</SPAN></SPAN>	123BRK</SPAN></SPAN>	aaaa</SPAN></SPAN>
3</SPAN>	45LBR</SPAN></SPAN>	ertt</SPAN></SPAN>	45LBR</SPAN></SPAN>	ertt</SPAN></SPAN>		klj</SPAN></SPAN>	12MNG</SPAN></SPAN>	dddd</SPAN></SPAN>
4</SPAN>		k12kl</SPAN></SPAN>		k12kl</SPAN></SPAN>			12KRC</SPAN></SPAN>	dddd</SPAN></SPAN>
5</SPAN>	45SNG</SPAN></SPAN>	ertt</SPAN></SPAN>	45SNG</SPAN></SPAN>	ertt</SPAN></SPAN>			1237</SPAN></SPAN>	3hhh</SPAN></SPAN>
6</SPAN>		ghjh</SPAN></SPAN>		ghjh</SPAN></SPAN>
7</SPAN>	WWWWWWWWW</SPAN></SPAN>
8</SPAN>	2468</SPAN></SPAN>	4</SPAN></SPAN>
9</SPAN>		klj</SPAN></SPAN>
10</SPAN>	LLLLLLLLLLLLLLLLLLLL</SPAN></SPAN>
11</SPAN>	123BRK</SPAN></SPAN>	aaaa</SPAN></SPAN>
12</SPAN>	12MNG</SPAN></SPAN>	dddd</SPAN></SPAN>
13</SPAN>	12KRC</SPAN></SPAN>	dddd</SPAN></SPAN>
14</SPAN>	1237</SPAN></SPAN>	3hhh</SPAN></SPAN>
15</SPAN>
16</SPAN>

Please note that everything is same as before and the difference is only that I added the code of every item. Before each list consisted of single column items but now from two columns. 

Many thanks!

Krasimir

XOR LX · Mar 28, 2014

Ah, then apologies, but in my opinion the work is becoming far too complex.

I strongly suggest you re-arrange your data set-up so that it can be better interpreted without going to such lengths.

Regards

lechev65 · Mar 28, 2014

I see ... please give me an idea about the re-arrangement.

Have a nice weekend!

Many thanks.
Krasimir

Formula listing items from several columns in one column

lechev65

Board Regular

XOR LX

Well-known Member

Excel Facts

lechev65

Board Regular

lechev65

Board Regular

lechev65

Board Regular

XOR LX

Well-known Member

lechev65

Board Regular

XOR LX

Well-known Member

lechev65

Board Regular

XOR LX

Well-known Member

lechev65

Board Regular

Similar threads

Forum statistics

Share this page

Formula listing items from several columns in one column

Board Regular

Well-known Member

Excel Facts

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock