Hi all,
I'm looking for some help on finding either a VBA code (probably) or formula that will address the following problem. The data in A-H below is from a CSV that draws from our software database. The CSV lists all of the prices for each product -- regular, post (sale), and deep post (...better sale) and the dates for which the sale prices are in effect, so for instance with product No. 501 the regular price is 60 but it's on sale for 54 from July-Dec. Columns J and K are supposed to get the regular, post, and deep post prices for each item on the first line of the item. This would be fairly easy with IF formulas but as you can see here there is a lot of variation in the way the data shows up -- some items cover 6 lines, some only 1, some have a deep post price and some don't...my formulas are about 5 IFs deep at this point and still not encompassing all the possible variations. And of course, the data changes every time you import a new CSV.
What I'd love is a way for excel to identify groups of data -- that is, look through column A and recognize that rows 1-2 are item 501, for instance -- and then take all the unique prices in column H from those rows and arrange them horizontally across cells in descending order. For product 550, therefore, I'd end up with 148, 138, and 115 in columns I-K in the first row of 550 (while leaving the data that's there alone). Is this at all possible?
Thanks for any help you can give!
I'm looking for some help on finding either a VBA code (probably) or formula that will address the following problem. The data in A-H below is from a CSV that draws from our software database. The CSV lists all of the prices for each product -- regular, post (sale), and deep post (...better sale) and the dates for which the sale prices are in effect, so for instance with product No. 501 the regular price is 60 but it's on sale for 54 from July-Dec. Columns J and K are supposed to get the regular, post, and deep post prices for each item on the first line of the item. This would be fairly easy with IF formulas but as you can see here there is a lot of variation in the way the data shows up -- some items cover 6 lines, some only 1, some have a deep post price and some don't...my formulas are about 5 IFs deep at this point and still not encompassing all the possible variations. And of course, the data changes every time you import a new CSV.
What I'd love is a way for excel to identify groups of data -- that is, look through column A and recognize that rows 1-2 are item 501, for instance -- and then take all the unique prices in column H from those rows and arrange them horizontally across cells in descending order. For product 550, therefore, I'd end up with 148, 138, and 115 in columns I-K in the first row of 550 (while leaving the data that's there alone). Is this at all possible?
Thanks for any help you can give!
