Formula listing items from several columns in one column

L

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.
[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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sorry, I miss to mention that the formula doesn't work and returns "#N/A" in each cell in the column A.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi XOR</SPAN>
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. </SPAN>
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.</SPAN>

Sheet2 </SPAN></SPAN>
[TABLE="width: 701"]
<TBODY>[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]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD="colspan: 2"]LLLL</SPAN>[/TD]
[TD="colspan: 2"]MMMM</SPAN>[/TD]
[TD="colspan: 2"]YYYY</SPAN>[/TD]
[TD="colspan: 2"]ZZZZ</SPAN>[/TD]
[TD="colspan: 2"]WWWW</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]123BRK</SPAN>[/TD]
[TD]aaaa</SPAN>[/TD]
[TD]56TRN</SPAN>[/TD]
[TD]ggjh</SPAN>[/TD]
[TD]9876</SPAN>[/TD]
[TD]mnmn</SPAN>[/TD]
[TD]5432</SPAN>[/TD]
[TD]ndnd</SPAN>[/TD]
[TD]2468</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]12MNG</SPAN>[/TD]
[TD]dddd</SPAN>[/TD]
[TD]45LBR</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD]13GBR</SPAN>[/TD]
[TD]23qwq</SPAN>[/TD]
[TD] [/TD]
[TD]vvrvr</SPAN>[/TD]
[TD] [/TD]
[TD]klj</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]12KRC</SPAN>[/TD]
[TD]dddd</SPAN>[/TD]
[TD] [/TD]
[TD]k12kl</SPAN>[/TD]
[TD] [/TD]
[TD]msms</SPAN>[/TD]
[TD]23MNV</SPAN>[/TD]
[TD]nnnn</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]1237</SPAN>[/TD]
[TD]3hhh</SPAN>[/TD]
[TD]45SNG</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD]3451</SPAN>[/TD]
[TD]ghhg</SPAN>[/TD]
[TD] [/TD]
[TD]3214</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]ghjh</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]23NRT</SPAN>[/TD]
[TD]nnnn</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]223m</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]frfd</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=10></COLGROUP>[/TABLE]


Sheet1

[TABLE="width: 701"]
<TBODY>[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]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD="colspan: 2"]MMMM</SPAN>[/TD]
[TD="colspan: 2"]MMMM</SPAN>[/TD]
[TD="colspan: 2"]WWWW</SPAN>[/TD]
[TD="colspan: 2"]LLLL</SPAN>[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]56TRN</SPAN>[/TD]
[TD]ggjh</SPAN>[/TD]
[TD]56TRN</SPAN>[/TD]
[TD]ggjh</SPAN>[/TD]
[TD]2468</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]123BRK</SPAN>[/TD]
[TD]aaaa</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]45LBR</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD]45LBR</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD] [/TD]
[TD]klj</SPAN>[/TD]
[TD]12MNG</SPAN>[/TD]
[TD]dddd</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD] [/TD]
[TD]k12kl</SPAN>[/TD]
[TD] [/TD]
[TD]k12kl</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12KRC</SPAN>[/TD]
[TD]dddd</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]45SNG</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD]45SNG</SPAN>[/TD]
[TD]ertt</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1237</SPAN>[/TD]
[TD]3hhh</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD] [/TD]
[TD]ghjh</SPAN>[/TD]
[TD] [/TD]
[TD]ghjh</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD="colspan: 2"]WWWW</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]2468</SPAN>[/TD]
[TD]4</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] [/TD]
[TD]klj</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="colspan: 2"]LLLL</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]123BRK</SPAN>[/TD]
[TD]aaaa</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]12MNG</SPAN>[/TD]
[TD]dddd</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]12KRC</SPAN>[/TD]
[TD]dddd</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]1237</SPAN>[/TD]
[TD]3hhh</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] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]

[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=10></COLGROUP>[/TABLE]

Thank you again in advance!
Krasimir
 
Upvote 0
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
 
Upvote 0
Hi XOR,</SPAN>
I am sorry for my poor explanation.</SPAN>
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.</SPAN>
EXAMPLE</SPAN>
Sheet2</SPAN>
[TABLE="width: 781"]
<TBODY>[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]I</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD="colspan: 2"]LLLLLLLLLLLLLLLLLLLL</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]MMMMMMMMMM</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]YYYYYYYYYYYYYYYY</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]ZZZZZZZZZZZZZZZZZ</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]WWWWWWWWW</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]123BRK</SPAN></SPAN>
[/TD]
[TD]aaaa</SPAN></SPAN>
[/TD]
[TD]56TRN</SPAN></SPAN>
[/TD]
[TD]ggjh</SPAN></SPAN>
[/TD]
[TD]9876</SPAN></SPAN>
[/TD]
[TD]mnmn</SPAN></SPAN>
[/TD]
[TD]5432</SPAN></SPAN>
[/TD]
[TD]ndnd</SPAN></SPAN>
[/TD]
[TD]2468</SPAN></SPAN>
[/TD]
[TD]4</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]12MNG</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[TD]45LBR</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]13GBR</SPAN></SPAN>
[/TD]
[TD]23qwq</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]vvrvr</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]klj</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]12KRC</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]k12kl</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]msms</SPAN></SPAN>
[/TD]
[TD]23MNV</SPAN></SPAN>
[/TD]
[TD]nnnn</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]1237</SPAN></SPAN>
[/TD]
[TD]3hhh</SPAN></SPAN>
[/TD]
[TD]45SNG</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]3451</SPAN></SPAN>
[/TD]
[TD]ghhg</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]3214</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]ghjh</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]23NRT</SPAN></SPAN>
[/TD]
[TD]nnnn</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]223m</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]frfd</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

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.</SPAN>
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.
</SPAN>
Example
</SPAN>
List</SPAN>
[TABLE="width: 253"]
<TBODY>[TR]
[TD="colspan: 2"]LLLLLLLLLLLLLLLLLLLL - TITLE</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]123BRK</SPAN></SPAN>
[/TD]
[TD]aaaa</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]12MNG</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]12KRC</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1237</SPAN></SPAN>
[/TD]
[TD]3hhh</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]code</SPAN>
[/TD]
[TD]item description</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Example:</SPAN>
Sheet1</SPAN>
[TABLE="width: 842"]
<TBODY>[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]I</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD="colspan: 2"]MMMMMMMMMM</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]MMMMMMMMMM</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]WWWWWWWWW</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]LLLLLLLLLLLLLLLLLLLL</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]56TRN</SPAN></SPAN>
[/TD]
[TD]ggjh</SPAN></SPAN>
[/TD]
[TD]56TRN</SPAN></SPAN>
[/TD]
[TD]ggjh</SPAN></SPAN>
[/TD]
[TD]2468</SPAN></SPAN>
[/TD]
[TD]4</SPAN></SPAN>
[/TD]
[TD]123BRK</SPAN></SPAN>
[/TD]
[TD]aaaa</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]45LBR</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]45LBR</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]klj</SPAN></SPAN>
[/TD]
[TD]12MNG</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]
[/TD]
[TD]k12kl</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]k12kl</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]12KRC</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]45SNG</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]45SNG</SPAN></SPAN>
[/TD]
[TD]ertt</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1237</SPAN></SPAN>
[/TD]
[TD]3hhh</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]
[/TD]
[TD]ghjh</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]ghjh</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]WWWWWWWWW</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]2468</SPAN></SPAN>
[/TD]
[TD]4</SPAN></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]
[/TD]
[TD]klj</SPAN></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="colspan: 2"]LLLLLLLLLLLLLLLLLLLL</SPAN></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]123BRK</SPAN></SPAN>
[/TD]
[TD]aaaa</SPAN></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]12MNG</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></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]12KRC</SPAN></SPAN>
[/TD]
[TD]dddd</SPAN></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]1237</SPAN></SPAN>
[/TD]
[TD]3hhh</SPAN></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]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

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. </SPAN>

Many thanks!</SPAN>

Krasimir </SPAN>
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
497
mumps
mumps
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
252
myall_blues
myall_blues
S
  • Solved
Differences in two date sets calculated in one cell
Replies
3
Views
356
Fluff
Fluff
K
  • Question Question
conditional formatting question
Replies
2
Views
568
kerm007
K
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,445
Messages
6,172,177
Members
452,446
Latest member
walkman99

Share this page

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