Return multiple Occurrences on same row

[ElmerS]

Hi,

My question is presented, in details, inside the WB.

Thanks, Elm

[NBVC]

To do it without helper columns is quite cumbersome... what you can do is just the font in the helper columns to match the background so essentially they become invisible...

Having said that... in D9 enter:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

in E9 enter:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down

in C9, then enter:

Please Login or Register  to view this content.

copied down.

Hide D9:E15 by changing font to match background.

[Steve R]

Amazing, i did it!

See attached file.

[ElmerS]

NBVC:

Thank you. That works fine.
I assume there is no way to insert the first formula into the second in order to avoid the helper columns - or... !?

Steve:
Thank you too, but:
1) when I tried to produce (by copying your formula into another range of cells I got an #VALUE! error.
2) There is no comma between the digits.
Instead of 2,7 there is 2.7 and instead of 5,0 it returns only 5.
(I know I can alter the decimal digits but I need a comma - as string - and NOT a thousand separator used in some countries).

Elm.

[NBVC]

NBVC:

Thank you. That works fine.
I assume there is no way to insert the first formula into the second in order to avoid the helper columns - or... !?


Elm.

Not really... because the second formula depends on the result of the first for counting and determining what Small() function k factor to use...and so I would get circular references...

[Steve R]

Steve:
Thank you too, but:
1) when I tried to produce (by copying your formula into another range of cells I got an #VALUE! error.
2) There is no comma between the digits.
Instead of 2,7 there is 2.7 and instead of 5,0 it returns only 5.
(I know I can alter the decimal digits but I need a comma - as string - and NOT a thousand separator used in some countries).

Elm.

1. You need to press Ctrl + Shift + Enter
More info about array formulas
2. I converted the result to string values. The values use commas but i think it has to do with my country settings?

[ElmerS]

Hi, Steve

For answer 1: that much I know (Ctrl+Shift+Enter).

2) I tried to copy your formula (from the Formula Bar ! ) and pasted it into 2 columns but Nothing happened.

Pls look at col. M & N.

If I understood your formula, (and I didn't examined it to the end), - it can be typed in any Column - not only in col. B, as you did.

What did I do wrong ? Where is my mistake ?

Thanks for your time, Elm

[NBVC]

Okay,

I got it to work without helper columns...

try this formula in C9, confirmed with CTRL+SHIFT+eNTEr and copied down

Please Login or Register  to view this content.

[ElmerS]

Thanks again, but I must have done something wrong, again, because I don't get the requested results.

Pls take a look at the attached WB.

[NBVC]

No, it was me who goofed.. I was checking the results against the wrong thing....

...back to square one... using helper columns for now... I will try to work on it when I get enough time to dedicate to it.

[ElmerS]

Never mind, thanks anyway.

Take your time - I'll stick around...

PS:
I marked this thread as SOLVED but ,with your permission, I still look forward for a solution without an helper column.

[NBVC]

Although, I am not sure why the changing the font colour in the helper columns won't work for you... ?

[ElmerS]

I didn't said that.

The Question, as mentioned before, is solved.

I always like to prevent from using Helper Columns - if applicable - and it has nothing to do with hiding and/or "white coloring" its font.

Thanks.

[Steve R]

Hi, Steve

For answer 1: that much I know (Ctrl+Shift+Enter).

2) I tried to copy your formula (from the Formula Bar ! ) and pasted it into 2 columns but Nothing happened.

Pls look at col. M & N.

If I understood your formula, (and I didn't examined it to the end), - it can be typed in any Column - not only in col. B, as you did.

What did I do wrong ? Where is my mistake ?

Thanks for your time, Elm

This is what i see when i open your file "Return multiple Occurrences on same row1.xls"

See the attached gif file. It looks like the copied formula in col. M & N is working?

[Ron Coderre]

Using your posted workbook
For a "no helper column approach"....try this ARRAY FORMULA,
committed with CTRL+SHIFT+ENTER (instead of just ENTER):

Please Login or Register  to view this content.

Copy that formula into C10:C15

Edited to include this simpler version:

Please Login or Register  to view this content.

These are the results:

Please Login or Register  to view this content.

Is that something you can work with?
(Looks like SteveR and I converged on the same approach.)

[NBVC]

Well, there you go!

[ElmerS]

Yep, Ron, this must be the ultimate and shortest solution.

Steve,
Thanks for your efforts but could it be working for you because you are on "Excel 2007" !?
If it does - then it must be sort of a "bug" because the functions you use are well known from previous versions...

NBVC,
Thanks.
I still wonder about Steves formula not working as predicted on "Excel 2003".

I hope someone will check this out on another "2003" version.
What I mean by that is - copying Steves formula from the Formula bar and pasting it onto another column.

Elm.