Concatenate matching values

[questi]

I have an excel file with two columns:
Column A has the index number
Column B has the count (value of -2 to 10)

The list is used to keep count of my sons panini cards and see which ones are missing, column A are the card numbers, column B are the number of cards he still needs (two of each for him and his brother). -2 means he does not have this card, any number above 0 means he has a card to trade.
Example is like this:
A B
1 -1
2 -2
3 -1
4 0
5 4
6 1

Now we want to make a list of cards he is missing, cards his brother is missing and cards he has up for trade
So I need a formula to give me (preferably comma separated) list of Column A if Column B equals -2, the same for -1 and the same again for any number above 0 (so 3 lists in total but I guess the formula would be the same)
How can this be done?

[Ochimus]

Col A on attached lists card numbers.
Col B is the number your son possesses
Col C uses this formula to see how many he needs (as each son needs a pair)

IF(B3>=2,0,IF(B3<2,2-B3))

(If there are two or more cards, the son does not need any. If there are less than two, he needs (2-what there is)

Col D then uses this formula to see how many the brother needs:

=IF(B3>=4,0,IF(B3>2,1,IF(B3<2,2)))

(If there are four or more cards. the brother does not need any more. If there are more than two cards, he still needs one (because his brother has taken two already), otherwise he needs two of the card)

And Col E uses this formula to see how many "surplus" cards they have to trade for the ones they need:

=IF(B3>4,B3-4,0)

(If they have more than four cards, they can trade balance above four. Otherwise they have none to trade)

Hope this helps

Ochimus

[questi]

thanks for your help, I realize I have not been clear enough in what I want to achieve.
I want to have a single formula in a single cell to list all of the missing cards at once, then the same thing for the duplicates.
This is in order to paste the value of the cell later on and paste it into trading boards
I have attached the file without formulas but how it would look like in the end (just with 10 cards instead of thousands)
I tried attaching the file but with chrome if i click on the attach file it just shows a very small blank line, maybe i am not allowed to attach files because i am too new?
Yes, seems i am too new as i am also not allowed to paste a link or even a screenshot of it

EDIT: nevermind, file is attached

[questi]

nevermind, figured out how to attach after reading the huge yellow bar ^^
I need glasses -_-

[Glenn Kennedy]

In G3:
=TEXTJOIN(", ",TRUE,IF($B$3:$B$12=-2,$A$3:$A$12,""))

In G4:
=TEXTJOIN(", ",TRUE,IF($B$3:$B$12<0,$A$3:$A$12,""))

In G5:
=TEXTJOIN(", ",TRUE,IF($B$3:$B$12>0,$A$3:$A$12&" ("&SUMIF(B:B,$B$3:$B$12)&")",""))

You may need to use ; as the separator.

Showing my age... what is a Panini card??

[Glenn Kennedy]

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new user of this site, on this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

Regards,

Glenn.

[AliGW]

Showing my age... what is a Panini card??

Football collectables. Like a cross between cigarette cards and TopTrump cards.

[Glenn Kennedy]

and TopTrump cards???

[questi]

Thanks, will try to formulate a better topic next time
i dont know toptrump nor cigarette cards :P
Panini I know because my son started collecting them :-)

I opened the attached file and it looks just like what I need, however if I either update the formula or paste it into my own excel sheet, i get a "#NAME?" error
I assume this is because of the excel version? I am using Office from companies Office365 subscription, version inside excel says Microsoft Office Professional Plus 2016 (sounds like this isnt office365 but it should be ^^).
It seems the TEXTJOIN function is not available

[Glenn Kennedy]

I used TEXTJOIN because your PROFILE says O365. You should have said that you wanted it to work on older versions of Excel. You should change your profile to reflect what it says on the green box when you launch Excel.

Your options are:

Helper column

or

VBA.

Your choice.

[questi]

yes, i thought it was O365, will change my profile
What is a helper column?
I would probably prefer something without scripts if possible

[Glenn Kennedy]

A helper column is an additional column (or two) that is needed as an intermediate in producing the final result. It is very easy with VBA, but maybe a bit fiddly with a formula (for the last one)

[Glenn Kennedy]

In your case, you may need up to 4 helper columns... so a VBA solution would be MUCH easier and neater.

[Glenn Kennedy]

Here's the VBA version.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


You are using an older version of Excel than me. So, please refer to the attached file. The formulae used are array formulae.

These 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 the curly brackets { } 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...

[Ochimus]

Why not just concatenate the formulae from my original post (#2), as shown on the attached?

This in C3 and copied down:

=CONCATENATE(IF(B3>=2,0,IF(B3<2,2-B3)),",",IF(B3>=4,0,IF(B3>2,1,IF(B3<2,2))),",",IF(B3>4,B3-4,0))

Ochimus

[questi]

because that does not give me the output i need
I need to have a single concatenated cell with all cards (value from Row A) listed
the output you see in the other excel file from Glenn Kennedy is exactly what I need the output to be

[questi]

Glenn Kennedy: I originally pasted the code into the workbook as there was no module that popped up but then I looked at your workbook and saw it was in a module so I created a new module... works fine now!
Thank you!

[Glenn Kennedy]

It was in the instructions in my post... Line 4 of "How to install you new code"... Glad you like it. thanks for the rep.