Hi SamT thanks again and again to follow up with this...I give you a bit of background, Sheet 1 is what we actually have in the system, sheet 2 is what we SHOULD have.
So comparing the two sheets I can find what to adjust in the system, this adjustments include Currencies as we sometimes pay in USD and sometimes in EUR (depending on the region).
Nowhere in your code is the Concatenated string place in a cell. Neither is any cell that is being Concatenated checked to see if there are any currency sign in the value of the cell. IOW, any currency signs on the Worksheets are ignored in the above code. That is not what your code above is doing. The code above is checking each row of sheet1 to see if it randomly matches
any row of sheet2. IOW, If Row 999 matches sheet2 Row 77, then Sheet1 G999 will have a "V" in it.
I know the code makes that check, and when an entire row in Sheet1 is matching entirely a row in Sheet2 it gives back the "V" in Sheet1 column J, but in the code I added two formulas too, please see below:
WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,"" "",B2,"" "",C2,"" "",""Price"","" "",D2,"" "",""Freight"","" "",E2,"" "",""Duties"","" "",F2,))"
WkStg = "=IF(ISBLANK(RC[-7]),"""",IF(RC[-1]<>""V"",INDEX(Sheet2!C1:C7,MATCH(1,(RC[-7]=Sheet2!C1)*(RC[-6]=Sheet2!C2)*(RC[-5]=Sheet2!C3),0),7),""""))"
Those two formulas are kicked in when you DON'T have the " V" .
Respectively the first formula concatenates the cells from A:F in Sheet2 and the second match one of those concatenated rows with a row in Sheet1 that has got the same values in the first three cells.
That is on the purpose of easily find out WHY the sheet1 and 2 are not matching and what to correct in the system, in the file I have sent you there are no formulas because as you can read in the code at some point I get rid of the formulas copying and paste only values , that way the worksheet is much more lighter and you can work with filters ( sometimes I have more than 500 hundred rows).
Now..
what I would really need is that the concatenation of the rows in sheet 2 could maintain the format you have in column D & E in sheet2 (basically those two currency can only be either EUR or USD), if please look at the file I've attached you will see that the concatenation has been reported by index and match from sheet 2 to 1 for an easy check, but unfortunately I need also the currencies.
Please tell me if until here I managed to be clear...
Do you have any cells displaying two currency symbols at once?
No I don't, only one currency for each cell.
Please upload a workbook you we can understand the nuances of the sheets. Please delete all but a dozen or so Rows in each sheet, Some good Rows, but be sure and leave some Rows that don't match. One for each type of mismatch, please.
Use the Go Advanced Option and scroll down to see the Manage Attachments button.
I have already uploaded a file, is not visible to you? it was in my latest post before this one, please check so I know if I did it well,
I've now uploaded two files the workbook (the tool) and my personal file (otherwise you cannot run the tool, as far as I know).
The tool is loaded and with some errors too, you must simply:
- run the first macro pressing " PREPARE" button
- and then the check itself with the "START" button
THANKS THANKS
Reply With Quote