Struggling with VLOOKUP OR IF statement

[MikeJHHT]

I apologize if this sounds confusing in advance,

Column K contains a concatenate for the warehouse software i use for entering sales orders based off the first truck number.
Column R adds additional sales orders with the same truck number so it wont repeat step 1 which is K.

When i copy the sales order information into excel, it will fill in column B and D.

For every same customer, the truck numbers will be the same (unfortunately my example shows D11 should be with truck # 755 but customer service entered the name differently...

Is it possible if there is only truck number 750, column U will show (k2), but if there are more 750's it will add each additional cell from R

for example U2, should be =K2&R2&R3&R4&R5&R6&R7&R8&R9 (which is the start of the concatenate(K2) followed by the additions in (R)

also, im entering the truck numbers manually based off the similar customer names, is there a statement that would automate this?

Ive been struggling to search using the right terminology to get exactly what im looking for.
Thankyou as always for your time.

 

[MikeJHHT]

That's incredible!, its doing exactly what i imagined it would do, I really need to learn VBA.
I wasn't sure if this was something that would need to be made as a statement..

So after running it several times i ran into a small issue, i tried going through your code but i cannot decipher it unfortunately, just way to advanced.

When you run it if you notice 751's last sales order entered is J60850, which is also truck number 752. This will lock up the concatenate. I tried tweaking column R by raising everything up one cell but that didn't work.

750 worked
751 pulled a SALES ORDER from 752
752 worked
753 pulled a SALES ORDER from 754
754 worked
also 755 (R21) does not have a sales order since there's nothing for it to find, but it was added to the end of 755

So would this be a statement that could be entered in column R to feed the Macro? or should the macro do this.

Again, thankyou so much, this sounds silly but this is extremely exciting for me, i didn't think this automation process would be possible

 

[MikeJHHT]

sorry i posted this twice and could delete it so i just edited it

 

[MikeJHHT]

mass.shiplist MAGIC.xlsm
	A	B		D				H		J	K	L	M	N	O	P	Q	R	S	T
1	TRUCK #	SO'S		CUSTOMER							BEGINNING OF TRUCK SETUP					ADDITION TO SAME TRUCK #			GROUPING CODE BY COL D
2	750	J68944		1137 J & O DISTRIBUTING						START	010;200709;750;01;Y;25;S;J68944;T1;16:30;END					S;J68944;T1;16:30;END			010;200709;750;01;Y;25;S;J68944;T1;16:30;END
3	751	J53329		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J53329;T1;16:30;END					S;J53329;T1;16:30;END			010;200709;751;01;Y;25;S;J53329;T1;16:30;END
4	751	J59359		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J59359;T1;16:30;END					S;J59359;T1;16:30;END			S;J59359;T1;16:30;END
5	751	J60322		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J60322;T1;16:30;END					S;J60322;T1;16:30;END			S;J60322;T1;16:30;END
6	751	J62989		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J62989;T1;16:30;END					S;J62989;T1;16:30;END			S;J62989;T1;16:30;END
7	751	J63874		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J63874;T1;16:30;END					S;J63874;T1;16:30;END			S;J63874;T1;16:30;END
8	751	J65429		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J65429;T1;16:30;END					S;J65429;T1;16:30;END			S;J65429;T1;16:30;END
9	751	J67573		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J67573;T1;16:30;END					S;J67573;T1;16:30;END			S;J67573;T1;16:30;END
10	751	J69102		1171 WOOD HEAT LLC							010;200709;751;01;Y;25;S;J69102;T1;16:30;END					S;J69102;T1;16:30;END			S;J69102;T1;16:30;END
11	752	J60850		8010 AMBLER FIREPLACE AND PATIO							010;200709;752;01;Y;25;S;J60850;T1;16:30;END					S;J60850;T1;16:30;END			010;200709;752;01;Y;25;S;J60850;T1;16:30;END
12	753	J63490		8506*1 ALBER'S FIREPLACE, INC							010;200709;753;01;Y;25;S;J63490;T1;16:30;END					S;J63490;T1;16:30;END			010;200709;753;01;Y;25;S;J63490;T1;16:30;END
13	753	J63551		8506*1 ALBER'S FIREPLACE, INC							010;200709;753;01;Y;25;S;J63551;T1;16:30;END					S;J63551;T1;16:30;END			S;J63551;T1;16:30;END
14	753	J63861		8506*1 ALBER'S FIREPLACE, INC							010;200709;753;01;Y;25;S;J63861;T1;16:30;END					S;J63861;T1;16:30;END			S;J63861;T1;16:30;END
15	753	J66136		8506*1 ALBER'S FIREPLACE, INC							010;200709;753;01;Y;25;S;J66136;T1;16:30;END					S;J66136;T1;16:30;END			S;J66136;T1;16:30;END
16	754	J63307		A1347 KRINGS STOVES & FIREPLACES							010;200709;754;01;Y;25;S;J63307;T1;16:30;END					S;J63307;T1;16:30;END			010;200709;754;01;Y;25;S;J63307;T1;16:30;END
17	755	J57749		A8010 AMBLER FIREPLACE & PATIO							010;200709;755;01;Y;25;S;J57749;T1;16:30;END					S;J57749;T1;16:30;END			010;200709;755;01;Y;25;S;J57749;T1;16:30;END
18	755	J59601		A8010 AMBLER FIREPLACE & PATIO							010;200709;755;01;Y;25;S;J59601;T1;16:30;END					S;J59601;T1;16:30;END			S;J59601;T1;16:30;END
19	755	J60067		A8010 AMBLER FIREPLACE & PATIO							010;200709;755;01;Y;25;S;J60067;T1;16:30;END					S;J60067;T1;16:30;END			S;J60067;T1;16:30;END
20	755	J60347		A8010 AMBLER FIREPLACE & PATIO							010;200709;755;01;Y;25;S;J60347;T1;16:30;END					S;J60347;T1;16:30;END			S;J60347;T1;16:30;END
21	755	J60407		A8010 AMBLER FIREPLACE & PATIO							010;200709;755;01;Y;25;S;J60407;T1;16:30;END					S;J60407;T1;16:30;END			S;J60407;T1;16:30;END
22																S;;T1;16:30;END			S;;T1;16:30;END
23																S;;T1;16:30;END
24
WILL CALLS

Cell Formulas
Range	Formula
B2:B21,D2:D21	B2	=paste!A1
K2:K21	K2	="010;"&TEXT(TODAY()+1,"yymmdd")&";"&A2&";01;Y;25;S;"&B2&";T1;16:30;END"
S2:S22	S2	=IF(ISNUMBER(SEARCH(D2,D1)),P2,K2)
P2:P21	P2	="S;"&B2&";T1;16:30;END"
P22:P23	P22	="S;"&B23&";T1;16:30;END"

 

[MikeJHHT]

I hope this doesn't mess up too much. I added a new statement in column S to help assist in grouping the correct customers with the right truck numbers.
So now column S can determine when a new customer starts go back to K, if its the same customer, use P.
Column D if looking at the customer above it, if different, then K, if same, then P.

Will these help your macro build together the concatenates?

 

[MikeJHHT]

After running the macro, i noticed this part, not only is it taking K3 but also P3

010;200709;751;01;Y;25;S;J53329;T1;16:30;END;S;J53329;T1;16:30;END;S;J59359;T1;16:30;END;S;J60322;T1;16:30;END;S;J62989;T1;16:30;END;S;J63874;T1;16:30;END;S;J65429;T1;16:30;END;S;J67573;T1;16:30;END;S;J69102;T1;16:30;END;S;;T1;16:30;END;S;;T1;16:30;END;;;;;

 

[mumps]

Click here to download your file. It looks like the macro worked properly. If it hasn't, please point out in detail how it is not working referring to specific cells, rows and column.

 

[MikeJHHT]

V3 is grouping the data for all truck numbers associated with 751... But, B11(J60850) which is part of the next truck number is at the end of the concatenate in V3
B11 is also in V11 where it should be.

I believe i messed up on the grouping arrangement in COLUMN R which may have messed with your macro, so i redid it, shown above in column S to allow the grouping to possibly be easier

 

[mumps]

I'm a little confused now. Cold you upload a copy of your revised file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Please explain again in detail what you would like to do referring to specific cells, rows, columns and worksheets. Manually include the expected results in the appropriate columns so I can see what they should look like. If the workbook contains confidential information, you could replace it with generic data.

 

[MikeJHHT]

ok i will work on this this afternoon, i really appreciate your help, and your also helping me better understand how to convey issues precisely with excel. Its easy to spit out what's in my head forgetting someone has to decipher my nonsense into sense. So thankyou again. I will get this over to you with the revision and expectations as well as the mockup of what i see it being.

 

[MikeJHHT]

lets see if this works
Columns B-D are Pasted Data(work orders) from another program
Column A im currently entering manually; assigning truck numbers starting at 750 to same customer names in Column D.
Can column A automatically number the TRUCK #'S based off same data in Column D?
ex: If customer WOOD HEAT is in D3 to D10, then A3 to A10 will be the same truck number, for every customer, a different truck number will be assigned.

Column K is the first part of the concatenate, If there is one truck number ex: 750 (A2), only K2 will be used, refer to V2
If there are additional Truck numbers, Column P will be used 2nd.
-----K2 and P2 cannot be used together, There is an SO(sales order) in Column B, which is placed in both K and P, Column S decides if its the header or a secondary.
EX. Truck number 751(A3)has same date to A10. Therefor the order would be K3 for the first, then P4 to P10 for the additional. refer to V3
A11 if by itself and will only need K11 refer to V11
A12(753) has the same data as A13 to A15 therefor the order would be K12&P13&P14&P15 as entered in V12

I would like U and V to look like this.
When the file is opened those fields will be blank
The data will be pasted in Columns B-D,
Then click the "MAGIC" button in H1, and the data will fill in U and V.
Then the user can take the combined concatenate and paste it into another application.