Formula to convert 1 column/row to matrix

Hi Everyone
First of all, I'm come from Vietnam so I'm sorry for my bad English.
I dont know how to describe the question more clealy so please see the attached file for details.
Any help would be appreciated.

Welcome to the forum!

Your two arrays look identical to me, so I have no idea what you are trying to do.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

So this is homework?

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

See post #5.

Hi AliGW
Because of it is homework then I tried my best to get the answer by myself.
The original question is get the smallest value of sum start-end point of each code.
I have idea fr it but I just cannot find the solution how can I convert an array such as
{-2.8;0;0;0;-17.8;-2.5;0;0;0;0;-15.9;0} (1 row)
to {-2.8,-17.8;-2.5,-15.9} (2R x 2C)
Since I'm new with array formula then I start from here.
Hope you can give me some hints.
Tks.

If you're trying to transpose the data so that, for example, all B210 values are in the first row of the array, all B211 values in the second row, etc. Then you need to step back and look at it differently.

That would only work if the result would be an eqaul number of columns for every row, meaning that you would need to have the same number of every code in your data source. In your sample, you have 5 of the first code and 6 of the second, so the theory fails before you begin.

Is the data sorted by code, or is it permissable to do so? Match functions love sorted data

@Ali. I just solved it using the {1} method that we were discussing last week. Do you have any alternative thoughts?

I'm afraid not at the moment. I'm not really quite getting what the OP is after. And I'm just about to go offline for the evening.

I'll be interested to see how this has developed when I look in again in the morning.

Post deleted

For the benefit of others:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Code	Value				Hi everyone. I'm so sorry for my bad English.
2	B210	-2.8	0			I have a homework about using just 1 array formula sum 2 values of start and end-point then compare them to get smallest value
3	B210	9.9				B210 starts at row 2, ends at row 6.
4	B210	-5.7				B210 starts at row 7, ends at row 12.
5	B210	-13.6				I have to sum value at B2 with B6, sum value at B7 with B12 and then compare them to get smaller value
6	B210	-17.8				I mean
7	B211	-2.5				(-2.8) + (-17.8) = (-20.6)
8	B211	7.9				compare with
9	B211	7.9				(-2.5) + (-15.9) = (-18.4)
10	B211	-4.7				So min(-20.6,-18.4) is -20.6
11	B211	-12.3				The real data is very large, not just B210 and B211. Maybe B212, B213 … and so on. So I need a generic formula for it.
12	B211	-15.9
13						My idea is get values like this (array formula in C2)
14						{-2.8;0;0;0;-17.8;-2.5;0;0;0;0;-15.9;0}
15						Then by some how we have
16						{-2.8,-17.8;-2.5,-15.9}
17						Finally we using mmult and min like this
18						MIN(MMULT({-2.8,-17.8;-2.5,-15.9},{1;1}))
19						result is -20.6

Sheet: Sheet1

So, to summarise:

1. You want to take the first and last entries for each code number and add them together.
2. Then you want to find the minimum of all these numbers.

You are correct that your final MMULT formula gives the correct answer.

Yes - I understand. Thanks for clarifying.

When you say this is homework, what is the level of the course you are undertaking?

Jason - I think it's this one in C2 (array entered):

=IFERROR(($B$2:$B$12)/(($A$2:$A$12<>$A$3:$A$13)<>($A$2:$A$12<>$A$1:$A$12)),0)

If you evaluate, you should see the array generated.

This is your actual data. As per your file you only add code / value only.
Actually if your data with some logical, its make the difference. i.e. Invoice-Number, Invoice-Date, Code, Nam, Value. or some more.

In class, we will tell the ideas to solve an exercise and then discuss which idea is the best and how to do it with array formula.

Ah! So your teacher doesn't know how to do it yet, either? Well, that makes sense.

I think the way forward is to discuss it here, too.

This is your actual data. As per your file you only add code / value only.

Given what the OP has just told us, i.e. this is all theoretical/exploratory, I think we have to assume that he has given us enough relevant information.

For Post #20 "Congnt92.
As my thinking, you want sum first & last value of particular code.
In this case you create add one more column for example "Date".
Secondly, you pull of unique code in another column. Then start & end date & applying sum formula with criteria.
Between sum range you can pull out Min.
For more clarity refer attach file.

AVK - that makes the problem very easy to solve, however I really believe the challenge is to do it without helper columns. The OP has specifically stated this in post #20:

... how to do it with array formula.

I think we need to stick to that brief.

Originally Posted by congnt92

My teacher will give us the answer in the next week.

When he does, please post it here for those who have been trying to help you.

Thanks.

Hi Jason.
It is my mistake.
The formula must be
IFERROR(($B$2:$B$12)/(($A$2:$A$12<>$A$3:$A$13)<>($A$2:$A$12<>$A$1:$A$11)),0)
Tks.

Bosco - this requires each of the code sections to be defined. The idea is not to have to do this. The OP wants a formula that will work with any size of dataset with any number of code sections.

Please read through post #14 to understand what is required.

The thread to which Jason refers is here: https://www.excelforum.com/excel-for...n-the-fly.html

Originally Posted by Bosco

=MIN(SUM(INDEX(B2:B12,N(IF(1,AGGREGATE({15,14},6,ROW(A2:A12)-ROW(A1)/(A2:A12="B210"),1))))),SUM(INDEX(B2:B12,N(IF(1,AGGREGATE({15,14},6,ROW(A2:A12)-ROW(A1)/(A2:A12="B211"),1))))))

Hi Bosco,
Thank for your help but I need for a generic formula. With it we can solved not for only 2 codes.

Originally Posted by jason.b75

Found a way to get your 2 column array, but there is still work to do. ... I've done it using a different approach, with 5 functions nested inside the first array of MMULT without transposing ...

I was think about it too. But I still not get it to work.

Originally Posted by AliGW

The thread to which Jason refers is here: https://www.excelforum.com/excel-for...n-the-fly.html

Thank, Ali. Very useful link. Although it seems not related with this topic but i've learn about using array value for vlookup with {1} technique.

It is related to this topic, I can assure you, because Jason has solved your problem using the techniques discussed there and shared that solution with me.

As this is homework, he won't share his solution with you here until after you have posted the solution given by your teacher. However, if you post ideas that you might have regarding the VLOOKUP trick, I am sure he will let you know if you are on the right track or not.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Think about the role of the four different arguments in the VLOOKUP function. One of them is the key to being able to generate an array with all of the numbers you need. Also, remember that Jason said this:

I've done it using a different approach, with 5 functions nested inside the first array of MMULT without transposing.

Originally Posted by congnt92

May I must combine vlookup and lookup ??

That's not how I did it, but it will work.

Have you heard of approximate match with vlookup, or similar functions?

edit:- maybe not so simple with lookup. My first working formula was to combine vlookup and lookup, but that used more functions than vlookup alone.

Well done! It's different from Jason's solution but uses the same techniques.

Hi Jason
From the given link, I understand that T() will convert a range to "array reference", right?
Also, my teacher show us the solution for this excercise.
- Create a vector of start and end point of each code: {1;5;6;11} (note: area is A2:A12)
- Convert a vector to {1,5;6,11} using AGGREGATE(15,6,vector,k) with k is a matrix like this {1,2;3,4}
The technique is:
(ROW(1:2)-1)*2+{1,2}
In my example, we have 2 codes so the generic formula is (ROW(1:n)-1)*2+{1,2}
- Using offset() to get {-2.8,-17.8;-2.5,-15.9}
Min and MMULT will do the rest job.
Full formula is:
MIN(MMULT(N(OFFSET($B$1,AGGREGATE(15,6,MATCH($A$2:$A$12,$A$2:$A$12,{0,1})/($A$2:$A$12<>$A$3:$A$13),(ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($A$2:$A$12,$A$2:$A$12))))-1)*2+{1,2}),)),{1;1}))

So the thing we learn from this example just simple is technique to convert an 1 R/C array to matrix.
Thanks Ali, Jason, Bosco, avk for your help.

P/S: I still follow this topic to get more great methods from you.
Tks.

I think the challenge is still on: can anyone crack this to work with the codes in any order?

This has been a particularly enjoyable thread because of the working out we have seen along the way.

So can it be done with a random order in the values column?

Match() requires data must be sort with a specific order for match_type 1/-1. AGGREGATE helps us avoid it.
But at least if we are using AGGREGATE then the data must be contiguous (no need to A-Z or Z-A).
Because AGGREGATE(15,...) sort position of start and end point of each code from smallest to largest.
If B210 start 1, end 5, B211 start 2 end 7 then AGGREGATE will return {1,2;5,7} and then the result will not true. (it must be: {1,5;2,7})
If the data is contiguous then this will work

=MIN(MMULT(N(OFFSET($B$1,AGGREGATE(15,6,ROW(1:11)/((COUNTIF(OFFSET($A$2,,,ROW(1:11),),$A$2:$A$12)=1)+(COUNTIF(OFFSET($A$2,,,ROW(1:11),),$A$2:$A$12)=COUNTIF($A$2:$A$12,$A$2:$A$12))),{1,2;3,4;5,6}),)),{1;1}))

The technique to create {1,2;3,4;5,6} we already know.
Capture1.PNG