Need formula instead of sorting

[JK1234]

I have a spreadsheet that has original data. I originally used the array formula after sorting. But my application is different now. I can not use the sort function or any macros so it has to be a function or filter. Been playing with this one for a while but cant figure it out. Please see file. Also the array wont work if there is a gap in the data. The data needs to be in the final list (green) in order and only one of each can show. Basically I want to show only one of each value or item and then find the sum of that value.

Thanks

[JK1234]

Just found out that I can not use an array formula.

[Palmetto]

Not exactly sure what you want. I suspect your sample workbook may not be truly reflective of your actual workbook. If it is not then the suggestions below may not be what you need, in which case upload a workbook that does reflect your real structure and type of data.

However, the attached show two possible solutions.
1. Formula: ="item "&ROW() -- and -- =SUMIF($B$19:$B$28,$F1,$C$19:$C$28)

2. Pivot Table (no blank rows or columns allowed)

[JK1234]

Thanks for response but I can not use pivot tables either.

And the other version does not work either. The file must work with blank spaces.The actual file starts at B18:C30

Then what i need is them in order like G19:H22.

I will have blank spaces and need them to print out (in order) only one instance of each and then total that.

[Palmetto]

You're really limiting yourself by not using Pivot Tables and performing a sort on the data. Why these limitations (or preferences)?

I see your results table is in alphabetical order and this is not easily accomplished with a formula, especially given multiple entries exist.

You could used Advance Filter to extract a list of unique values (for the item names), copied to a new location and then perform the sum using SUMIF.

Or, considering sorting the original data in place, forcing blanks to the bottom and then using the built-in SUBTOTAL feature which will allow you to collapse the rows and show only the totals.

I don't understand why you aren't taking advantage of the applicable tools to get the results you need. Use a separate worksheet if necessary so that the original data is untouched.

[JK1234]

I am using the excel file to create other pages - like HTM. So the excel version is for some and htm is for others. And there is alot to maintain so doing one in excel in one way and the other in another wont work. Alot easier to build in excel and then transposed page over into htm or asp.
So what works in excel has to work in htm.

This is actually working well except some of the functions wont convert/switch over so my I am looking for functions instead of my vb/tables/macros that I am using.

I think this problem may not get fixed for this page.

[JK1234]

I can get the autofilter to work just like sort, but can it be done automatically every time there is a change in the data (without macros)

I still need a function to sort data. (no macros, arrays, has to be a function or combination of)

[DonkeyOte]

As Palmetto has already outlined your restrictions make things a little muddled.

It's not clear whether "helpers" are permitted or not - I'm assuming they are - so you could, using your last file, do the following:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

which gives you the count of unique items in your list... this helps you reduce number of calcs performed in results table such that

Please Login or Register  to view this content.

use of G19:G30 is to account for possibility of B19:B30 being filled with unique entries

[JK1234]

thanks - I thinnk I got it..please check file.

The answer in blue is what I needed.Looks as if it works with the data.

[DonkeyOte]

Sorry my bad - I didn't post that correctly did I...

Please Login or Register  to view this content.

no need for I19:I30

[JK1234]

thanks..works that way. I;ll play with it for more data and see how it works on the real sheet

Thank you sir...so cool how you did it.

[JK1234]

Please Login or Register  to view this content.

it didnt like that code, but I did get it working properly in excel. So you were right except the conversion did not support this function.

Not sure if the others work due to they are dependent upon this one. So now I am still looking.

[JK1234]

Here is a response I got back from the company's converter program:

Arrat formulas are not supported. You will have to return the cells one at a time instead of as an entire range (for example A76,A77,A78... instead of A76:A78).

I dont understand their comment?? and how it will work??

[DonkeyOte]

I have no idea either.

You need to check with whomever it is you're liaising to establish whether by arrays they're referring to Array formulae [1] or in fact to Arrays of values [2]

[1]: traditionally referred to as CSE (ie { }) - SUMPRODUCT could be classed as an Array as it is processed in the same way despite not requiring CSE entry (ie no { } )

[2]: eg LARGE(array,k)

why not just ask "them" to tell you how to do it - or ask someone familiar with whatever converter programme you're using - I'm afraid at this juncture I myself have no idea...

[JK1234]

its spreadsheetconvert.

They dont always seems to get back to me. I write a few emails and they only answer one. I bought their 4.6 verion and was thinking about the asp.net one. But if I cant convert this, my whole project wont work, This sorts my menu items from like 60 rows, omits the blanks and adds them up.

I cant even get them to send me a list of supported function, etc.

gets frustrating...I got a great project but Excel wasnt the answer. I can not find anyone that has it to use my program. So I need to rewrite my stuff using functions that will work. Please see new sheet. This is what I have to work with.

[DonkeyOte]

I know I sound like a bore but if you don't know what is / is not supported then there's not a great deal we can do at this point.

If you can not refer to multi-cell ranges then it's going to get so ugly it's not true....

I don't really understand the overall context in which this is being used (ie interactivity with "converted" file etc) - presumably you are aware that you can "publish" excel sheets as static html pages ... if interactivity is key and this is proving too troublesome you might perhaps consider Google Docs ?

To reiterate though - at this point I can't really add anything further... unless anyone else has ideas ?

[JK1234]

I am trying to publish the excel sheet as html, but not all excel functions are supported so the page does not work. I actually have may pages that are converted, just this one page wont work. I had to change alot of macros and such to forumulas and functions, I actually got them all working but a few but this page would be great if worked. It is the shopping cart. I can not stay in excel and google wont work. ALl my pages need to be in Excel.

At least I got a prototype done in excel just need to make it work in another format. I was hoping that I could have used something else in Excel to make it work.

I still cant get a whole list of supported functions from them.
Trust me this is frustrating but I am working to find a solution. I do thank you so much. I may use your ideas though in my Excel version still. So thanks not a total waste of effort or time. Man, I am so close to in converting all my stuff over.