concatenate the currency too

isasa74 · #16 12-15-2015, 08:20 AM

Quote:

Originally Posted by charlesdh

Thanks.

I'll have to check on this tomorrow. Perhaps another will pick up.

THANKS so much, no problem I can wait, I hope you can help me but if it is too much job for you I can understand

charlesdh · #17 12-15-2015, 01:06 PM

Hi,

I modified my code so that it will populate sheet1 column "H" with the data from sheet 2.

Check this copy.

isasa74 · #18 12-15-2015, 02:19 PM

Hi Charlesdh thanks again for helping me

, thanks for the new version, the code does the correct concatenation but actually without the index/match the code cannot match the exact concatenation with the correct row in sht1

My comments:

the index/match formula in sht1-H is meant to match the right combination of vendor-plant-material between the rows in sht1 and sht2, without this formula the concatenation of sht2 is not matched with the correct correspondent row in sht1. This is crucial as the order of the rows is random and the same material can have different plants and different vendors (that is why sort A-Z the materials wouldn't work)

in my opinion the easiest way would be to have always all the rows in sht2 concatenated in sht2-G, and then index/match formula in sht1-H looking for the exact combination Vendor-Plant-Material and reporting the concatenation when the exact match is found. That way, no matter the order or the quantity of the materials, we will always have at the end of the check the correct concatenation reported in the correct row in sht1 for the comparison.

weirdly the function at the end of the code that makes the check " currency sensitive doesn't work anymore with both version of you codes :

Code:

Function CurAdj(WkVal As Range) As String
Dim WkF As String
   WkF = WkVal.NumberFormat
   CurAdj = IIf(InStr(1, WkF, "$$") <> 0, "$", IIf(InStr(1, WkF, "$¤") <> 0, "¤", "")) & WkVal
End Function

charlesdh · #19 12-15-2015, 02:36 PM

OK,

I see what you are referring too. I'll have to do some search on this.

isasa74 · #20 12-15-2015, 02:53 PM

THANKS

Maybe the best way would be to stick , as much as possible, with the old structure of the tool ,that even if a bit bizarre, works fine for everything but the concatenation of the correct currency formats.

Thanks so much again!

charlesdh · #21 12-17-2015, 12:19 PM

Hi,

I hate to say this. But, this has me stumped. I can usually find a answer. But...
That being said I was able to modify the function. It will set the "CurAdj to the correct format. This line of code shows the correct format,

Code:

With WSh2
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
'---
      For I = 2 To LR
         ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
         ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/")) = Empty
      Next I

ValD and ValE have the correct format. But, when this is (I think) translated to
"D2" and "E2" in this line of code

Code:

         WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,""  "",B2,""  "",C2,""  "",""Price"",""  "",D2,""  "",""Freight"","" "",E2,""  "",""Duties"","" "",F2,))"

D2 and E2 does not have the correct format.
Unfortunately I'm not familiar with "Scripting.Dictionary". So this is a problem for me.

Here's the Function code that worked for me.

Code:

' This fuction obviously not mine :) is meant to make the check for giving the "V" format sensitive and it works GREAT.
Function CurAdj(WkVal As Range) As String
Dim WkF As String
   WkF = WkVal.NumberFormat
   CurAdj = IIf(InStr(1, WkF, "$") = 1, "$", IIf(InStr(1, WkF, "$€") = 2, "€", "")) & WkVal
End Function

Hopefully another member can help you.

isasa74 · #22 12-19-2015, 04:54 AM

thanks Charles for your help and I must say that until now I couldn't find anyone who can sort it out, it looks simple but it certainly must be not.
thanks again.

isasa74 · #23 12-19-2015, 10:20 AM

I sorted it out with the following formula :

=IF(ISBLANK(A1),"",CONCATENATE(A2," ",B2," ",C2," ","Price"," ",IF(CELL("format",D2)="C2",TEXT(D2,"$0.00"),TEXT( D2,"€ 0.00"))," ","Freight"," ",IF(CELL("format",E2)=",2",TEXT(E2,"€ 0.00"),TEXT(E2,"$ 0.00"))," ","Duties"," ",F2))

charlesdh · #24 12-19-2015, 11:44 AM

HI,

It's great that you figured it out. But, let me ask you a question.
Does your program look at each row ( same row in each sheet) to see if there is a difference between sheet 1 and 2.
And, if a row in sheet1 does not match sheet2 your code will place a "V" in sheet1 to say there is a difference between sheet1 and 2 for the same row?
Your code will then populate sheet2 column "G" CONCATENATE "A" thru "F". Which will then go to sheet1 at the point where sheet1 does not have a "V".
Is this basically what your code does?

isasa74 · #25 12-20-2015, 06:01 AM

Hi Charles,

My code does:

Concatenates the first 6 columns in Sheet2-G
Compare all rows in Sheets1 to rows in Sheet2 (rows can have different order in the two sheets).
If rows match for every values then you have the " V " in sheet 1 for the matched row.(those rows are correct, Checked!)
When there is no entire match, so for the row in sheets that don't have the " V", the INDEX/MATCH formula in sheet1-H looks for the same combination "vendor/plant/part number" in sheets 2 and when found it reports the concatenation you have in sheet2-G for the row that match the combo "vendor/plant/part number".
That way I can easily compare the concatenation with the row in sheet1 to see why they don't match.

Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use Two (concatenate) Fields If Condition Is Met (If... Then... Else)	SoonerLater	Mail Merge	1	09-30-2015 05:40 PM
how to concatenate text box on new slide	wichitawx	PowerPoint	5	05-19-2015 05:17 AM
Concatenate with @ symbol	anickles	Excel	2	02-06-2015 09:39 AM
Concatenate if in descending order	pgeorgilas	Excel Programming	2	12-10-2014 01:35 AM
If, move and concatenate in VBA	devcon	Excel Programming	0	07-04-2011 12:44 AM