0

I'm trying to make Up-Sells list for my personal web shop but cannot figure it out. I have a lot of items in my database and I'm trying to sort them. I have unique ID's for categories and I need to paste every SKU which is under that category.

Input/Output

Input
SKU    Category
123       1
124       1
234       2
235       2

Output SKU    Category    Up-Sell
       123       1          123, 124
       124       1          123, 124
       234       2          234, 235
       235       2          234, 235
Davis Cibuls
  • 3
  • 1
  • What version of Excel? If you have the `TEXTJOIN` function, you can do it with a simple formula: `=TEXTJOIN(",",TRUE,IF(B2=Category,SKU,""))` entered with `ctrl+shift+enter`. If you have a version prior to 2016, you can do it with multiple formulas and helper columns, or simpler with a VBA routine – Ron Rosenfeld Feb 08 '17 at 13:18
  • I have 13 excel right now. Tried adding textjoin function through VBA, but no luck. Or I simply do not understand the formula you gave me. :( at start got #NAME, after VBA got #VALUE – Davis Cibuls Feb 09 '17 at 08:52
  • Post your VBA code by editing your question. – Ron Rosenfeld Feb 09 '17 at 11:32
  • Unfortunately lost everything as I did not save the document. What should I be writing in formula, where u typed Category and SKU. Does excel understand what it is or I should be typing cell numbers etc.? – Davis Cibuls Feb 09 '17 at 13:07
  • There is no way for Excel to understand what range you are referring to unless you tell it specifically. But you won't be able to use the `TEXTJOIN` function in Excel 2013. – Ron Rosenfeld Feb 09 '17 at 13:33
  • To expand a bit, you can use either cell addresses or Named Ranges. I find dynamic Named Ranges useful, especially when debugging. But there is a tradeoff for calculation time. Sometimes it is better to use "oversized ranges". EG, if you think you'll never have more than 5,000 rows, use something like $A$1:$A$50000 (and you can still Name it, to make your formula easier to understand). – Ron Rosenfeld Feb 09 '17 at 21:14

0 Answers0