vlookup with concatenate

[ramyahemanth]

sir,

i have a data containing name of the student , Registration no, fee receipt no., fee date & fee amount columns. The student may pay his fee in part, in different receipt no. (for eg. a few student will pay in 1 short or he may pay in 2 to 4 shorts(in one receipt or 3 more receipt)). Now i want to combine the receipt no. in one cell related to the same student and i also want "/" symbol between the receipt no. one after the other.

 

[uma.vattikuti]

I guess the details of receipt no. are in separate columns currently. If so, first use a if+concatenate functions first, to get the receipt no. in a single cell. Then apply vlookup wherever necessary.


The if+concatenate function would be like [=IF(C5="",B5,IF(D5="",B5&"/"&C5,IF(E5="",B5&"/"&C5&"/"&D5,B5&"/"&C5&"/"&D5&/&E5)))] when the receipt no. are in C5,D5,E5&F5.

 

[jason]

could you provide a sample worksheet as the layout of the data could make a big difference?!


if a student pays with multiple receipts, are these numbers entered in the same row across multiple columns or would each separate receipt be in a separate row for that particular student


BOB | Receipt#1 | Receipt#2 | Receipt#3 ....


or


BOB | Receipt#1 | Date | Amount

BOB | Receipt#2 | Date | amount

.

.

.

 

[ramyahemanth]

hello sir thanks for responding to my problem


as you asked for sample worksheet i am herewith sending the example

[pre]

SL. No.	  Name	        Registration no.   Date	       Ch. No.
1	Tushar Soni	1BY11CS083	09/08/2012	27044
2	Tushar Soni	1BY11CS083	09/08/2012	27042
3	Tushar Soni	1BY11CS083	09/08/2012	27043
4	Shruthi	        1BY09CS068	30/10/2012	25267
5	Shruthi	        1BY09CS068	21/11/2012	25274
6	Shruthi	        1BY09CS068	22/11/2012	25273
7	Shruthi  	1BY09CS068	09/01/2013	25283
8	Akhil Kumar	1BY11TE005	14/08/2012	27445
9	Akhil Kumar	1BY11TE005	16/08/2012	27444
10	A Manoj Kumar	1BY12EC001	29/08/2012	27644
11	Sowmya. K.R	1BY10MCA59	14/08/2012	26406
12	Sowmya. K.R	1BY10MCA59	16/08/2012	28404
13	Sowmya. K.R	1BY10MCA59	14/09/2012	28483

[/pre]
like this i have a 2000 no. of students details

Please do the neeful and thanks in advance

 

[bobhc]

Post edited to keep layout, if you put a back tick (key to the left of the number 1 key) before and after you samples/code then the indents are kept and it is easier for members to read.

 

[Faseeh]

Hi ramyahemanth,


I think you can use the UDF ConcIf() written by Luke here on this post:


http://chandoo.org/forums/topic/getting-a-concatenated-text-list-of-applicable-values#post-73544


Regards,