Forum Discussion

Copper Contributor

Nov 03, 2023

An UDF for Concatenate Function

Hello I was wondering whether it is possible to achieve this result using VBA/UDF? https://ibb.co/2yn729f Basically what I wanted to do is to be able to concatenate various string fr...

Formulas and Functions

Deleted

Nov 03, 2023

aijatiw Yes, you can create a VBA function but my question is why complicate it that much?

You could easily just have a column C with the below formula:
="Student with name "&A2&" is now on year "&B2

You can drag this formula down as many rows as you need. No need to be "typing the formula over & over again".

Copper Contributor

Nov 03, 2023

I'm sorry I think that I make myself unclear, because what I want is instead of typing =C35&C31&C36&D27, I want it to type =result(C31,D27) and it show the same result, so I was thinking how to put the "student with name " and "is now in year " like embedded on the sheet? I'm sorry if this confuses you I hope you get what I'm trying to say.

Because I need it to become as dynamic as possible. I will be dealing with more column and the result is not only to concatenate a row then drag it down,

Deleted

Nov 03, 2023

aijatiw Sure thing. I still wouldn't use VBA, you can just add two additional columns for your inputs:

The formula will now be =C2&" "&A2&" "&D2&" "&B2

If you're really, really desperate for a VBA solution I could look into it but honestly it's overkill for a simple task as this.

Copper Contributor

Nov 03, 2023

Thank you for you dedication to my problem.

Maybe I need to elaborate more clearly what I am trying to achieve. Here is a new image

https://ibb.co/44tSnkk

I have this fruit, qty and a when column. Then, I will share this excel file to other people, What I want to do is that they can get different result based on what cells or combination that they like.

How to do that? And also the data for each column are fixed but the result should be vary.

Bronze Contributor

Nov 04, 2023

aijatiw

Cartesian_product？

select * from basic_Cartesian_product;

with a as(select f01 from basic_Cartesian_product where f01<>''),

b as (select f02 from basic_Cartesian_product where f02<>''),

c as(select f03 from basic_Cartesian_product where f03<>''),

d as (select f04 from basic_Cartesian_product where f04<>'')

select f01||' '||f03||f02||' '||f04 result from a,b,c,d;

f01 f02 f03 f04

student with name	is now on year	Mike	1
		Donna	2
		Lula
		Naomi
		Luca

result

student with name Mikeis now on year 1

student with name Mikeis now on year 2

student with name Donnais now on year 1

student with name Donnais now on year 2

student with name Lulais now on year 1

student with name Lulais now on year 2

student with name Naomiis now on year 1

student with name Naomiis now on year 2

student with name Lucais now on year 1

student with name Lucais now on year 2

Resources