Single Cell call Multiple Record

[JAMIR]

Good Morning Everyone,

I have attached sample file.

I want to call the Vehicle No. in according to Depot from source table. I have tried vlookup, but its give only one record. Also i want to count the vehicles no. of perticular cell.

Please help..

Regards,


Zameer

 

[Nebu]

Hi:

Why can't you use a pivot to summarize.

Thanks

 

[JAMIR]

Hello Nebu Sir,

I have use pivot, i want to use it with array formula. B'coz its related with specific Report Format.

So, Can you please help me?.. It will highly appriciated.

Regards,

Zameer

 

[Nebu]

Hi:

Array formula will not give you multiple results in one cell, you need VBA to get the results as specified by you. Find the attached.

Thanks

 

[JAMIR]

Hello Sir,

Thanks for your immediate reply.

I am unknown about VBA. But i'll try to understand..

Its really helpful...

You are great NINJA

Take care

Regards,


Zameer

 

[JAMIR]

Hello Sir,

In cell the data look like. I want to give a space instead of , (Comma)
MH14BT0109 MH14BT3342 [MH14BT0109,MH14BT3342]

And after vehicle no. i want add vehicle type1 i.e.
MH14BT0109-ORD MH14BT3342-ORD

Thanks


Regards,


Zameer

 

[Nebu]

Hi:

Here you go.

Thanks

 

[bosco_yip]

Another option but in formula way,

1] Helper, G2 enter formula :

=B2&IFERROR(" "&VLOOKUP(A2,A3:G$10,7,0),"")

and, G2 >> Custom Cells Format by enter: ;;;

all copy down

2] Vehicle No, F13 formula copy down :

=IFERROR(VLOOKUP(A13,A$2:G$9,7,0),"")

and, cells alignment formatted with Wrap Text

3] No of Vehicle, G13 formula copy down :

=IF(F13="","",LEN(F13)-LEN(SUBSTITUTE(F13," ",""))+1)

Regards
Bosco

 

[JAMIR]

Hello Sir,

Thanks once again

Regards,

Zameer

 

[JAMIR]

Another option but in formula way,

1] Helper, G2 enter formula :

=B2&IFERROR(" "&VLOOKUP(A2,A3:G$10,7,0),"")

and, G2 >> Custom Cells Format by enter: ;;;

all copy down

2] Vehicle No, F13 formula copy down :

=IFERROR(VLOOKUP(A13,A$2:G$9,7,0),"")

and, cells alignment formatted with Wrap Text

3] No of Vehicle, G13 formula copy down :

=IF(F13="","",LEN(F13)-LEN(SUBSTITUTE(F13," ",""))+1)

Regards
Bosco

Hello Bosco

Its quits interesting.

Thanks

Warm Regards,

Zameer

 

[JAMIR]

Hi:

Here you go.

Thanks

Hello Nebu Sir,

I just reply for giving you thanks from bottom of my heart.

Sir, without your code i have lot of work. But with your code i just do my work like take ice-creame.

Its almost completed 7 months. And everything is working perfectly.

GOD BLESS YOU!!!!

Warm Regards,


Zameer

 

[John Jairo V]

Without helper columns, if you have Excel 2016, you can do this formulae (uses @bosco_yip workbook):

Array Formula (CSE) into F13:

=IFERROR(TEXTJOIN(" ",,INDEX($B$2:$B$9,N(IF(1,MODE.MULT(IF($A$2:$A$9=E13,(ROW($B$2:$B$9)-ROW($B$1))*{1,1})))))),"")

Blessings!