Help with calculated field

[Hnorgaar]

Hi there

Please take a look in the attached file, the question is how to create line F based on a formula

Thx Henrik

[Glenn Kennedy]

What's the maximum number of rows that will meet the concatenation criteria for each unique value in column G?

[Hnorgaar]

Max 4 of car model and 3 with kW

Like this + some empty rows

Leon, type 5F, 2013=>
Leon SC, type 5F, 2013=>
Leon FR, type 5F, 2013=>
Leon SC FR, type 5F, 2013=>
1.2l TSI 63 kW (CJZB) [7593/AHJ]; 1.2l TSI 77 kW (CJZA) [7593/AHK]; 1.4l TSI 90 kW (CMPA/CXSA) [7593/AHH]; 1.4l TSI 103 kW (CHPA) [7593/AHI]; 1.8l TSI 132 kW (CJS);
1.6l TDI 66 kW (CLHB) [7593/AHF]; 1.6l TDI 77 kW (CLHA) [7592/AHG]; 2.0l TDI 110 kW (CKFC) [7593/AHM]; 2.0l TDI 135 kW (CUPA) [7593/AHN]


Thx

[Glenn Kennedy]

An array formula, and a bit of a monster, but it seems to be working...

=IF(LEFT(E1,4)="Golf",

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G2)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),1)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G2)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),2)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G2)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),3)),""),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

Typo in formula. Amended here.

[Hnorgaar]

Thx, Unfortunate that wont do it as "Golf" is not unique, there are many models in my spreadsheet

An example from Alfa Romeo

1005 Giulietta, type 940, 2010=>2015 (not Facelift)
1005 1.4l TB 88 kW (198A4000); 1.4l TB Multiair 125 kW (940A2000)

[Glenn Kennedy]

Unrepresentative data!!

Easily fixed though. There's always a space in D. None of the cells are in D blank. So...

=IF(LEN(D1)>1,

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),1)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),2)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),3)),""),"")

[Hnorgaar]

Unfortunately it still doesnt work, but this solution is closest, I have added another sheet which shows the possible combinations Im dealing with, and the only problem now is line 11, 13, 15
I have tried to incorporate this solution, but it doesnt work either, seems to break something in your formula

IF(AND(ISNUMBER(SEARCH("kW";$H7))=TRUE;ISNUMBER(SEARCH("kW";$H8))=FALSE);H7;"")

Thx again for all your help

Henrik

Unrepresentative data!!

Easily fixed though. There's always a space in D. None of the cells are in D blank. So...

=IF(LEN(D1)>1,

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),1)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),2)),"")&

IFERROR(INDEX($H$1:$H$16,SMALL(IF(($G$1:$G$16=G1)*ISNUMBER(SEARCH("kw",$H$1:$H$16))*ROW($H$1:$H$16),ROW($H$1:$H$16),""),3)),""),"")

[Hnorgaar]

Thx a lot, that seems to work perfect, sved me a lot of trouble and time, owe you one

Another small thing you might be able to help with, please take a look on this formula

=IF(COUNTIF(Sheet2!$A$1:$A$47;H8)>0;H8;A7)

The problem is some lines are very long, more than 255 characters, do you have an alternate formula?

Thx Henrik

[Glenn Kennedy]

That's a separate Q. Please start a new thread.

For this thread, you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Hnorgaar]

Sorry to open this again, but the solution didnt work as else thought after further inspection.

I have copied all my rows this time and I refer to row 11, 13 and 15 as example, they are identical and should not be

Hope you might have time to take a look again, thx

Henrik

[AliGW]

You should remove the SOLVED tag from the thread.

[Hnorgaar]

You should remove the SOLVED tag from the thread.

You are very fast to respond

[AliGW]

Only if I happen to be around!

[Glenn Kennedy]

1. I don't want 3500 rows. Thirty will do.

2. Please repost the file WITH the formula in place.

3. Explain what the result should be for rows 11, 13 & 15 AND WHY.

I will be in and out today, as I replaced my ISP this morning and I am experiencing a few "teething problems".

[Hnorgaar]

Sorry about that, but it is difficult to give you a short list which show all the many combinations there are. However I try with this one and the formula I use in column F
is the one you gave me yesterday and this row is the culprit as the result there is the same for, as an example, row 11, 13 and 15 there, and shouldnt be.

Thx for your patience

Henrik

[Glenn Kennedy]

So... it seems that I have not understood your requirement.

The formula:

returns values only if column E is non blank
looks at all rows with the same "column G value" (why do you not have column headers???)
identifes those rows with kW in them (max 3 per column G value)
concatenates the kW-containg strings.

So if that's not what you want... what do you want?

'Cos based on that logic, rows 11, 13 & 15 will be the same.

Is it something to do with the fact that C11 is 1001 and C13/C15 are both 1002; and G11/G13/G15 are all 1002? I have no idea which column I should be using and what/which value is correct!!

[Hnorgaar]

Hi again

As the data come from row H, the kW are different for each model.Column C is a unique number for each model, column G is a unique number for the articles belonging to the group of cars in column H. What i need is to identify the exact cars belonging
to the group number in column H. However sometimes it is easy with 1 cell for model and below that one the engine identifier with kW, but sometimes its inconsistent and will have different combinations like in row 129 and 130 and sometimes is this
info in up to 6 rows like 4 models and 2 engines. Hope this inf is giving an idea. As to why it is wrong in row 11, 13, 15 is that 11 and 12 is one and 13 and 14 one and so on.So to sum it up, if there is only 1 cell with kW in Hper group number in G,
no combining, but if there is more, then the kW cells need to be combined, if there is more models in H, then both of them shall have same text in row F.
And where there is combining involved, your code is spot on now.

Henrik

Thx Henrik

[Glenn Kennedy]

RoFLAO.

Now I'm a bit lost... So should I be making a formula which:

returns values only if column E is non blank
looks at all rows with the same "column C value" (why do you not have column headers???)
identifes those rows with kW in them (max 3 per column C value)
concatenates the kW-containg strings.

If not, then what?? Don't worry, I have lots of patience.

[Hnorgaar]

Let see if I can make myself clear.
1. If E is blank is perfect
2. F is for your calculation and will be based on values in H AND same group number in G
3. With only 2 rows in H, one for model and one for kW is then just the kW cell goes in F, as in my attached sample, H12 goes to F11
4. When there are 2 or more models and 1 cell with kW, as in row 129 and 130, then they should have the same value in F based on the first value with kW in H, which are in in H131
5. When there are 2 or more models and more than 1 cell with kW, then they should have the same value in F based on the first value with kW in H combined with with the next one.
6. I think the max combination will be 4 models and 2 kW cells, so no, there can be more based on C value, 6 I think

Hope this works

Henrik

[Glenn Kennedy]

I'm out for a while.... look back later.

[Glenn Kennedy]

Try this:

=IF(LEN($E1)>1,

IFERROR(INDEX($H$1:$H$131,SMALL(IF(($C$1:$C$131=$G1)*($G$1:$G$131=$G1)*ISNUMBER(SEARCH("kw",$H$1:$H$131))*ROW($H$1:$H$131),ROW($H$1:$H$131),""),1)),"")&

IFERROR(INDEX($H$1:$H$131,SMALL(IF(($C$1:$C$131=$G1)*($G$1:$G$131=$G1)*ISNUMBER(SEARCH("kw",$H$1:$H$131))*ROW($H$1:$H$131),ROW($H$1:$H$131),""),2)),"")&

IFERROR(INDEX($H$1:$H$131,SMALL(IF(($C$1:$C$131=$G1)*($G$1:$G$131=$G1)*ISNUMBER(SEARCH("kw",$H$1:$H$131))*ROW($H$1:$H$131),ROW($H$1:$H$131),""),3)),""),"")

[Glenn Kennedy]

Typo in my formula. It's OK for 11, 13 & 15. However....

I's not OK at I40. Expalin again (soory) you expected answer here.

I40 is Model 1005.
H40 does not contain kW
I42 is another model (1006)
I42 does contain kW.

But you want I42 to appear in F40. Why???

[Hnorgaar]

I see what you get at, but the difference is only that there are 2 models with same horsepower, meaning sedan and cabriolet, and should therefore have the same text, which is the first cell with kW. So cell I40 should be the same as I41, and the only difference in I52-I57 is that there are 2 sets in same group nr, so same for them based on 2x3 cells
So based on the number in column G the the cells above the first cell coming with kW in column H should should have that text with kW, if there are more than 1 text with kW in 2 cells just below each other as in row 107-108, then the text should be combined
However the sheet attached above has it all correct except row 11, 13, 15
Forgot to answer your question,

I40 is Model 1005.
H40 does not contain kW "H42 does"
I42 is another model (1006)
I42 does contain kW. "H42 does"

[Hnorgaar]

I guess a better explanation is
C is not important, the important thing is the models has to be from the same group nr in column G

1 model, 1 kW= 1 model with kW
2 models, 1 kW= 2 models with the same kW
1 model, 2 or more kW= 1 model with combined kW
2 or more models, 2 or more kW=2 or more models with combined kW

[Glenn Kennedy]

Yes. I do need a better explanation. I'm not able to get to a PC for a short time...

[Hnorgaar]

Error, dont know how to delete

[Glenn Kennedy]

There has to be an easier way, but I've been staring at this for so long, I can't see my way through it. This monster delivers your expected results, though (column F).

[Hnorgaar]

However putting it into action in my spreadsheet, it seems to work perfect, havent found any error yet, maybe I will lol, guess it that have to do with you have deleted some rows affecting row A, guess a big thx again and will close the thread

Henrik

[Hnorgaar]

Except row 145,146,147

[Glenn Kennedy]

This is becoming too complicated for a formula, I think.

[AliGW]

Please select Thread Tools from the menu link above to mark this thread as SOLVED. Thanks.