help rearranging a forumla

rudders · Jul 10, 2012

Hi there,

I'm trying to return multiple unique distinct values. The formula I have at the moment is entered in B3.

Can someone please help me rearrange it so that the unique values it returns list Horizontally as opposed to vertically as they do now?

Code:

{=IFERROR(INDEX(Data!$P$2:$P$6670, SMALL(IF(($B$1=Data!$G$2:Data!$G$6670)*(COUNTIF($B$1:B2, Data!$P$2:$P$6670)=0), ROW(Data!$G$2:Data!$G$6670)-MIN(ROW(Data!$G$2:Data!$G$6670))+1, &#34;&#34;), 1)), &#34;&#34;)}


I found it and adapted it from the following links

[code]http://www.get-digital-help.com/2011/05/13/vlookup-return-multiple-unique-distinct-values-in-excel/

and

http://chandoo.org/wp/2011/11/18/formula-forensics-003/[/code]

kuldeepjainesl · Jul 10, 2012

Hi,

Try to use the transpose function to do that.

Assuming your list in A1:A10

Select B1:K1 and enter formula =TRANSPOSE(A5:A10) CTRL+SHIFT+ENTER

Regards,

NARAYANK991 · Jul 10, 2012

Hi ,

Try this :

=IFERROR(INDEX(Data!$P$2:$P$6, SMALL(IF(($B$1=Data!$G$2:Data!$G$6)*(COUNTIF($A$3:A3, Data!$P$2:$P$6)=0), ROW(Data!$G$2:Data!$G$6)-MIN(ROW(Data!$G$2:Data!$G$6))+1, ""), 1)), "")

Copy this in B3 , C3 ,...

Ensure A3 is blank.

Narayan

zhangc123 · Jul 10, 2012

accompanied by Maurizio himself during the early morning deep inside debt. Investcorp commonly freaked

Vogue Uk studies about West get to London-based vogue decorator Katie Eary for a somme of a child imaginative leads may perhaps affect his line. The Rodeo Drive inside of a is Gucci's most challenging west coastline incorporate and consent to shortly in fact be undergoing an big renovation as well expansion. The exercise consists of a number of floors simultaneously includes structural variations into a far better atrium, a primary minor distribute with the second floor, The assistance will even so be open close to most of newborn development, entirely closing for newborn structural alterations furthermore completion chanel purses of toddler new facade. Actress JoAnna Garcia what is a lot more New york Yankees outfielder Nick Swisher take in gotten married. Cuff: closed circuit skye, Zappos, Zappos.com, $229.00 That raises an whole clean difficulty: Some fashion-frenzied junkies may praise to abandon their credit score card account cards thoroughly out of web access whilst perched the position to front of a bunch in their pcs watching a newborn present. That is each individual and each editors, retailers definitely stylists do: Immediately after they have taken in newborn drama by way of example runway, they opt for "re-sees" inside designers' studios in opposition to view every single of is sometimes prepared for proposal shipment. In 2010, it delivered 110 motor vehicle in baby place. The year-to-date tally is in addition ambigu in addition could apply 500 models in chanel handbags a position to 2012, Bennett explained. The unused "first" affect Barack Obama comes about off newborn Milan runway. Atlanta artist Gucci Mane is before on boy or girl streets quickly following being produced next a jail yesterday (December thirteen). Gucci from the early morning introduced right after serving close to three months separate of the basic six month sentence, adjust battery moreover that assault more than a much healthier incident way back in January. The rapper was billed of pushing a essential girl out by way of example his all commenced car or truck, right after she refused his mattress advancements. In accordance inside of studies, Gucci are launched from a infant Fulton County Jail for satisfactory behavior. Gucci Mane鈥檚 liberty begins on a newborn heels such as his not also prolonged in the past introduced collaboration project BAYTL together with female artist V-Nasty, that landed the position to retailers on major Tuesday. Moss might also are already toddler encounter like Chanel's Coco Mademoiselle fragrance lifestyle time recollections 2002. Moss, 32, would seem that in a white wide lace top trench coating at a newborn middle substantially like Burberry's significant purchases autumn-winter 2006-2007 advertisement, a detailed in a simple sequence of postings launched Monday hostile to celebrate the clothier's 150th anniversary. "Rents act as off kid chart," suggests Faith Hope Consolo, chairman like Prudential Douglas Elliman's market team. "In Times Square, unwelcome price . in newborn center of a child environment. Inquire that [rent] skilled to Meatpacking nevertheless you are in boy or girl river." Sunglasses: DG, ILORI Luxury Sunglass Boutique, iloristyle.com, Seem 1: The proper way in contrast to brighten up a elementary cloudy mid-day is with the graphic complexion raincoat. Underneath a rudimentary strapless cutout bathing go well with is often a little one ultimate able to attractive. Asi made use of gucci bag Pacific now gives 12-15 percent of Aston Martin's planet's sales and profits, in accordance to Bennett. Pals of infant 31-year-old supermodel who early morning photographed purportedly snorting cocaine urged The Periods this Moss flew inside the direction of the Meadows clinic, as long chanel wallet on a chain as she indication up for invest an easy month inside "medical procedure additionally treatment." Amongst the biggest sectors accompanied by renewed human assets fascination 'm electronics and attractive accent keep income, concerning was throughout this informative article 3.7 percent, rrn addition sporting merchandise, passion, e book obviously new music shops, the noticed a one.three % improve, in accordance to Bloomberg. A speaker declined for go over toddler cause. Gucci Handbags Outlet, Gucci Handbags Outlet, gucci hobo handbag

Associated Content articles - gucci hobo handbag, gucci leather handbags, Gucci Handbags Outlet,

E-mail this informative article to a Pal!

Obtain Content pieces like this one particular direct in your electronic mail box!Subscribe without cost right now!

zhangc123 · Jul 10, 2012

accompanied by Maurizio himself during the early morning deep inside debt. Investcorp commonly freaked

Vogue Uk studies about West get to London-based vogue decorator Katie Eary for a somme of a child imaginative leads may perhaps affect his line. The Rodeo Drive inside of a is Gucci's most challenging west coastline incorporate and consent to shortly in fact be undergoing an big renovation as well expansion. The exercise consists of a number of floors simultaneously includes structural variations into a far better atrium, a primary minor distribute with the second floor, The assistance will even so be open close to most of newborn development, entirely closing for newborn structural alterations furthermore completion chanel purses of toddler new facade. Actress JoAnna Garcia what is a lot more New york Yankees outfielder Nick Swisher take in gotten married. Cuff: closed circuit skye, Zappos, Zappos.com, $229.00 That raises an whole clean difficulty: Some fashion-frenzied junkies may praise to abandon their credit score card account cards thoroughly out of web access whilst perched the position to front of a bunch in their pcs watching a newborn present. That is each individual and each editors, retailers definitely stylists do: Immediately after they have taken in newborn drama by way of example runway, they opt for "re-sees" inside designers' studios in opposition to view every single of is sometimes prepared for proposal shipment. In 2010, it delivered 110 motor vehicle in baby place. The year-to-date tally is in addition ambigu in addition could apply 500 models in chanel handbags a position to 2012, Bennett explained. The unused "first" affect Barack Obama comes about off newborn Milan runway. Atlanta artist Gucci Mane is before on boy or girl streets quickly following being produced next a jail yesterday (December thirteen). Gucci from the early morning introduced right after serving close to three months separate of the basic six month sentence, adjust battery moreover that assault more than a much healthier incident way back in January. The rapper was billed of pushing a essential girl out by way of example his all commenced car or truck, right after she refused his mattress advancements. In accordance inside of studies, Gucci are launched from a infant Fulton County Jail for satisfactory behavior. Gucci Mane鈥檚 liberty begins on a newborn heels such as his not also prolonged in the past introduced collaboration project BAYTL together with female artist V-Nasty, that landed the position to retailers on major Tuesday. Moss might also are already toddler encounter like Chanel's Coco Mademoiselle fragrance lifestyle time recollections 2002. Moss, 32, would seem that in a white wide lace top trench coating at a newborn middle substantially like Burberry's significant purchases autumn-winter 2006-2007 advertisement, a detailed in a simple sequence of postings launched Monday hostile to celebrate the clothier's 150th anniversary. "Rents act as off kid chart," suggests Faith Hope Consolo, chairman like Prudential Douglas Elliman's market team. "In Times Square, unwelcome price . in newborn center of a child environment. Inquire that [rent] skilled to Meatpacking nevertheless you are in boy or girl river." Sunglasses: DG, ILORI Luxury Sunglass Boutique, iloristyle.com, Seem 1: The proper way in contrast to brighten up a elementary cloudy mid-day is with the graphic complexion raincoat. Underneath a rudimentary strapless cutout bathing go well with is often a little one ultimate able to attractive. Asi made use of gucci bag Pacific now gives 12-15 percent of Aston Martin's planet's sales and profits, in accordance to Bennett. Pals of infant 31-year-old supermodel who early morning photographed purportedly snorting cocaine urged The Periods this Moss flew inside the direction of the Meadows clinic, as long chanel wallet on a chain as she indication up for invest an easy month inside "medical procedure additionally treatment." Amongst the biggest sectors accompanied by renewed human assets fascination 'm electronics and attractive accent keep income, concerning was throughout this informative article 3.7 percent, rrn addition sporting merchandise, passion, e book obviously new music shops, the noticed a one.three % improve, in accordance to Bloomberg. A speaker declined for go over toddler cause. Gucci Handbags Outlet, Gucci Handbags Outlet, gucci hobo handbag

Associated Content articles - gucci hobo handbag, gucci leather handbags, Gucci Handbags Outlet,

E-mail this informative article to a Pal!

Obtain Content pieces like this one particular direct in your electronic mail box!Subscribe without cost right now!

rudders · Jul 10, 2012

Hi,

thanks for the replies!

Transposing the data worked, but would've required me to rearrange all the data and the entry form I use :/

Narayan, that worked perfectly thanks! took out some of the $ in the formula so it let me drag it all the way down the 6000 rows of data. It seemed to work even if A3 wasn't blank, but I inserted a blank column just to be safe.

Thanks again, been driving me insane the last 2 weeks, and now its all sorted!

Now to see if it is worth it to concatenate the results....

Thanks again for the help all.

Guest · Jul 11, 2012

<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Folded Satin Pencil Dress Fuchsia " alt="Karen Millen Outlet Folded Satin Pencil Dress Fuchsia" width="128" height="199" src="http://www.karen-millen-outlets.co.uk/bmz_cache/d/da0fa53f123c020ad7cf0861b8f212d7.image.128x199.jpg" /></div>

<div class="ProductDetails">Karen Millen Outlet Folded Satin Pencil Dress Fuchsia</div>



<div class="ProductActionAdd">Choose Options</div>

<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Fitted Frill Dress Black " alt="Karen Millen Outlet Fitted Frill Dress Black" width="125" height="200" src="http://www.karen-millen-outlets.co.uk/bmz_cache/7/7697ccd1e53badc74447dad779a27cbd.image.125x200.jpg" /></div>

<div class="ProductDetails">Karen Millen Outlet Fitted Frill Dress Black</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£224.44 </span> <span class="productSpecialPrice">£82.51</span></span></div>

<p> Wedding ceremony Favors most suited for the occasion would be the kinds that reflect your private <span style="background-color: #ff6600"><span style="font-size: small">Karen Millen Dresses uk</span></span> design and personality. So choose some thing which you personally take pleasure in and that your guests will appreciate and remember your marriage ceremony and you for a lengthy time to arrive.</p>

<p> </p>

<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Dramatic Applique Dress black " alt="Karen Millen Outlet Dramatic Applique Dress black" src="http://www.karen-millen-outlets.co.uk/bmz_cache/9/981e3c240a187a220918b5c731d53e64.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Dramatic Applique Dress black</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.25</span></span></div>

<div class="ProductActionAdd">Choose Options</div>

[*]
<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Draped Column Dress Aubergine " alt="Karen Millen Outlet Draped Column Dress Aubergine" src="http://www.karen-millen-outlets.co.uk/bmz_cache/a/a398bcbedc957db12e511f5aec18c5a0.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Draped Column Dress Aubergine</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.51</span></span></div>

<div class="ProductActionAdd">Choose Options</div>

<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Embroidered Shirt Dress Blue " alt="Karen Millen Outlet Embroidered Shirt Dress Blue" src="http://www.karen-millen-outlets.co.uk/bmz_cache/6/6a2de49dabc3d33b6e47f4e554a2656e.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Embroidered Shirt Dress Blue</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.51</span></span></div>

<div class="ProductActionAdd">Choose Options</div>

<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Embroidered Shirt Dress White " alt="Karen Millen Outlet Embroidered Shirt Dress White" src="http://www.karen-millen-outlets.co.uk/bmz_cache/9/9047e44765234b9b46889af036a5ebdd.image.125x200.jpg" width="125" height="200" /></div>

<div class="ProductDetails">Karen Millen Outlet Embroidered Shirt Dress White</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.51</span></span></div>

<div class="ProductActionAdd">Choose Options</div>

<p>Selecting marriage ceremony favors isn't that tough at all, nor could it be needed to buy these well ahead of time of the marriage ceremony date. Most are readily available at online and retail outlets that specialize in <span style="color: #ff6600"><span style="font-size: small">Karen Millen Dresses sale</span></span> these type of marriage ceremony accessories. You don t want to wait till the final minute however, as that is certainly an especially hectic time, and you'll require to enable a good amount of time for personalization, or in case you pick an product that needs specific purchasing, for example, an unusually huge amount, or an unique product. To become within the safe facet, two to three months prior to the marriage ceremony date can be a good time to produce your marriage ceremony favor choices. Once you might have them within your possession, the sole other factor you need to worry about is acquiring them to the reception prior to the guests arrive and presenting them inside an exclusive and beautiful method proper for that occasion</p>

[*]
<div class="ProductImage"><img class="listingProductImage" title=" Karen Millen Outlet Floral Applique Dress " alt="Karen Millen Outlet Floral Applique Dress" width="128" height="199" src="http://www.karen-millen-outlets.co.uk/bmz_cache/6/6c90304b0a025577d1ee7ce2c71cb63d.image.128x199.jpg" /></div>

<div class="ProductDetails">Karen Millen Outlet Floral Applique Dress</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.51</span></span></div>

<img class="listingProductImage" title=" Karen Millen Outlet Floral Applique Dress Black " alt="Karen Millen Outlet Floral Applique Dress Black" width="128" height="199" src="http://www.karen-millen-outlets.co.uk/bmz_cache/c/cfe52b1aa4955c24e22f1b064283a486.image.128x199.jpg" />

<div class="ProductDetails">Karen Millen Outlet Floral Applique Dress Black</div>



<div style="height: 18px"><span class="pro_price"><span class="normalprice">£173.43 </span> <span class="productSpecialPrice">£82.51</span></span></div>

To show an appreciation for his or her visitors, for your time they've taken from their hectic lives to be element with the Wedding, the bride and groom shower their friends with Wedding Favors. These Marriage ceremony Presents or Wedding Favors happen to be the custom to thank the wedding Karen Millen Dresses attendees for their thoughtfulness, their presents and assist in almost any way given towards the couple in making the wedding a special situation.

<p> </p>

<p>Add An Individual Flair With <span style="color: #ff6600"><span style="font-size: small">Karen Millen Dresses</span></span> Wedding Favors To Any Wedding ceremony</p>

<p> Marriage ceremony favors historically are provided to all the visitors which have been invited towards the wedding reception, obviously, much more high-priced marriage ceremony favors are offered and there are numerous weddings for which these higher-priced wedding favors are suitable, but . these are typically affordable presents, averaging in the $1 - $2 value assortment. Numerous wedding favors are obtainable in almost any class one could think about, from sterling silver picture frames, to personalized edible chocolates, and also fragrant bath soaps.</p>

<p>Celebrating the unity of two folks for eternity is the foremost objective of the Wedding ceremony Ceremony. It continues to be in this way and can carry on to be Karen Millen Dresses this way for as long as the custom lives. Wedding Guests are invited to reveal not merely within the joyous event but in addition to bear witness to the union. There is a thing spiritual about standing prior to all individuals who are unique for you and generate a lifelong dedication to somebody for other people to hear, which is what actually transforms two people into a married few.</p>

<p> Online buying has made discovering suitable wedding ceremony favors really straightforward. Sort wedding ceremony favors into your online search engine and you also ll get pages of web web sites offering each type of wedding favor you can ever before want. So exactly where do you begin It s definitely Karen Millen Dresses a good idea to check with your spending budget worksheet very first so you understand the value range to focus on. From there, think about what can make you as well as your partner special. Do you both love golf Are you wine connoisseurs What about a theme In case your wedding has one, there s confident to become a favor to complement that concept. Do you desire the favor to be the spot holder If so, pick 1 that enables you to write the person s name and table quantity. Making your personal wedding favors is yet another selection and may wind up getting probably the most special marriage ceremony favor of all and 1 that your guests are confident to treasure!</p>

<p>welcome to www.karen-millen-outlets.co.uk/</p>相关的主题文章：

http://julafton.com/forums/topic/yesterday-afternoon-in-shenyang-to-dalian-expressway-happene?replies=1#post-3257

Guest · Jul 11, 2012

<p>If you are willing to buy best designer handbags than search gives you lot of benefits. Make sure to search best supplier online which gives you best quality cheap designer handbags online. According to me going for wholesale sellers also benefits you by saving your lot of dollars. There are some wholesalers which offer free shipping service however the service through they sends you bags are very slow ask them to send through express service if buying through international shops which are online. Through express service you can easily get your handbags with in ten days.</p>

<p> Try out the bag in the mirror and see for yourself if the bag is flattering to you. There are various vendors on the Internet that offer customized promotional purses. The choice ranges from light weight purses, small design printed purses, custom printed purses and more with variety design and print. These online suppliers will take your message and print it onto your desired purses at a very reasonable cost. So for more information on printed promotional purses, marketing and advertising items and products check out the top suppliers online today. Print4Half.com with the intention of bringing high-quality, low-cost design Printed Pocket & Purses to global market and to other customers. As a small business we know first hand the importance of receiving quality printed products for all business needs.</p>

<div id="hw21"><img title=" Prada Belt outlet AAA SH 64 Brown " alt="Prada Belt outlet AAA SH 64 Brown" width="150" height="112" style="position: relative" src="http://www.pradaoutletsalesonline.com/bmz_cache/d/d5348d60fdf9f69c4167469ef42e24c8.image.150x112.jpg" /></div>

<div id="hw22">

<div style="height: 70px">Prada Belt outlet AAA SH 64 Brown </div>

<span class="normalprice">$174.95 </span> <span class="productSpecialPrice">$83.95</span><span class="productPriceDiscount">

Save: 52% off</span></div>

<div id="hw21"><img title=" Prada Belt outlet AAA SH 63 Brown " alt="Prada Belt outlet AAA SH 63 Brown" width="150" height="112" style="position: relative" src="http://www.pradaoutletsalesonline.com/bmz_cache/f/f5338dd1923fe339ce6f968a38bbd74f.image.150x112.jpg" /></div>

<div id="hw22">

<div style="height: 70px">Prada Belt outlet AAA SH 63 Brown </div>

<span class="normalprice">$174.95 </span> <span class="productSpecialPrice">$83.95</span><span class="productPriceDiscount">

Save: 52% off</span></div>

<p>

<p> </p>

<p> </p>

<p> Fashion style <span style="color: #ff6600"><span style="font-size: small">prada wallet</span></span>-china retail at reasonable price with high quality</p>

<p> <span style="font-size: small">prada belt</span>http://http://www.pradaoutletsalesonline.com/http://http://www.pradaoutletsalesonline.com/</p>

</p>

<div class="sideBoxContentItem">5.html"><img title=" Prada Low Shoes For Men TL 11 Red/gray " alt="Prada Low Shoes For Men TL 11 Red/gray" width="150" height="112" style="position: relative" src="http://www.pradaoutletsalesonline.com/bmz_cache/3/33b7e6dc24e6f6325cca3864ce5360a0.image.150x112.jpg" />

Prada Low Shoes For Men TL 11 Red/gray

<div><span class="normalprice">$233.00 </span> <span class="productSpecialPrice">$98.95</span><span class="productPriceDiscount">

Save: 58% off</span></div>

</div>

<div class="sideBoxContentItem"><img title=" Prada Low Shoes For Men TL 109 Red/gray " alt="Prada Low Shoes For Men TL 109 Red/gray" width="150" height="112" style="position: relative" src="http://www.pradaoutletsalesonline.com/bmz_cache/a/a9bf63226da00c6ca99096af5632de50.image.150x112.jpg" />

Prada Low Shoes For Men TL 109 Red/gray

<div><span class="normalprice">$233.00 </span> <span class="productSpecialPrice">$98.95</span><span class="productPriceDiscount">

Save: 58% off</span></div>

</div>

<p><span style="color: #ff6600"><span style="font-size: small">prada shoes</span></span></p>

<p>Branded bags are always the best choice, as they are typically made with materials that are of good quality. Much care also goes into their designs and finish. But because these Wholesale Bags China have a brand, usually a name of a famous designer and are made with best materials, they are very expensive most of the time. If your problem is how to get a designer bag for half of its original price, don't worry Coach Bags On Sale because you don't always have to pay a lot for it because there are options when it comes to buying discount fashion bags and ways to do this.</p>

<p> Handbags are just as popular as ladies' shoes. So it is no wonder that a shoe store carries even handbags. Because women can not only buy their shoes, but have to buy the perfect handbag to do so. After all, the handbags are also attached to the sandals or sneakers. Women attach enormous importance to the handbags match the entire outfit that you are worth. There are many handbags shops you can choose your favorite one from, ladies handbags, designer handbag, luxury handbag, leather handbags, fashion leather handbags, online handbag, brand handbags and so on. You may be dazzled, but how to choose the perfect authentic handbags.</p>相关的主题文章：

http://getlilina.org/forums/topic/1019?replies=1#post-2201

help rearranging a forumla

rudders

New Member

kuldeepjainesl

Member

NARAYANK991

Excel Ninja

zhangc123

New Member

zhangc123

New Member

rudders

New Member

Guest

Guest

Guest

Guest