One Excel file with two columns that is populated with a description code and a description. The total number of lines is 30 000 and the description is on the Languages of English, Spanish and Portuguese.
I need to indicate in the third column all the descriptions that are not English.
I have a spreadsheet that loads a huge amount of data (50,000 rows) that needs to be parsed as follows:
Below are the Broker Names listed in Column A:
Column A:
Broker Name 1 = Tom
Broker Name 2 = Dick
Broker Name 3 = Harry
Broker Name 4 = Frank
Below are the Broker Codes listed on another section of the same worksheet:
Column R (for example):
Broker Code for Broker Name 1 (Tom) = BT
Broker Code for Broker Name 2 (Dick) = BD
Broker Code for Broker Name 3 (Harry) = BH
Broker Code for Broker Name 4 (Frank) = BT
I would like to have the Broker codes (e.g. BT, BD, BH or BT) put in Column C next to the corresponding Broker names. The part that I am having difficulty with is that it is a mixed situation whereby only one Broker Code (e.g. BD and BH) applies to one specific Broker Name, while other Broker Codes (e.g. BT) may apply to multiple Broker Names.
Can a formula be developed that could be copied and pasted in Column C that would compare the list of codes against the list of names and return the appropriate Broker Code?
I got some 10 digit numbers in column A, need to get the marking in column B as per the criteria given below for last 5 digit numbers. The checking should start with platinum. on getting a macro or any formulas for each criteria.
Category- For Last 5 Numbers MARKING Samples
Numbers Ending with Double Digit SILVER 9280015622
Ending with 786 (Without 0) SILVER 9280025786
First 2 Digits-0 & Last 3- From 101-998 SILVER 9280000101-998
Double Digit Repetition with 0 Separator GOLD 9280055055
Numbers in Sequence GOLD 9280012345
Numbers Ending with Triplets GOLD 9280052333
Numbers in Round Hundreds GOLD 9280082500
Numbers in Round Thousands GOLD 9280065000
Ending with Triplet GOLD 9280052333
Mirror Image Numbers GOLD 9280012321
Triplets with 0 as Prefix or Suffix GOLD 9280010444, 9280014440
Numbers having 786 (Others 0) XCLUSIVE 9280000786, 9280100786
Single digit Ending (All Others 0) XCLUSIVE 9280000001-9
Triplet followed by Double Zeros XCLUSIVE 9280055500
2 Consecutive Triplets XCLUSIVE 9280111555
Ending Quadruplet XCLUSIVE 9280056666
Last 4 digit 0 PLATINUM 9280010000
All Penta Numbers Including 0 PLATINUM 9280011111
Working for a dutch company and using a lot of excel and access it is sometimes a pain that when you are looking for functions. for example "left(b3;3)" in dutch is "links(b3;3)" now this is an easy one but some are hard to guess.
I wrote some neat code which condionally formats cell's in an Excel range and dependend on a value in column "Q" (= status) then sets the color of the field. it works fine
However when working with an english version of Excel I need to use "left" instead of "links"
how to check the local settings for Excel?
[code]
'Conditional Format status cell's
.Range("G4:P" & (xRcount + 3)).Interior.ColorIndex = 4
iRow = 4
iRow2 = 4
I need a function to return a boolean T/F to check if a 17 character string meets the following checksum validation.
This is for Vehicle Identification Numbers for vehicle made after 1981
I need a VBA function that I can call multiple times in a workbook,
Example =VIN(A1) which returns True or False.
First, find the numerical value associated with each letter in the VIN. (I, O and Q are not allowed.) Digits use their own values.
A->1B->2C->3D->4E->5F->6G->7H->8
J->1K->2L->3M->4N->5P->7R->9
S->2T->3U->4V->5W->6X->7Y->8Z->9
Second, look up the weight factor for each position in the VIN except the 9th (the position of the check digit).
1st->82nd->73rd->64th->55th->46th->3
7th->28th->1010th->911th->812th->713th->6
14th->515th->416th->317th->2
Third, multiply the numbers and the numerical values of the letters by their assigned weight factor, and sum the resulting products. Divide the sum of the products by 11. The remainder is the calculated check digit. If the remainder is 10, the calculated check digit is the letter X.
Finally, if the calculated check digit did match the 9th digit of the VIN entered by a user, the VIN passed the checksum test. It failed the checksum test otherwise.
I have this excel sheet with 200+ (number can change depend of situation) checkboxes here and I would like for the worksheet to do the following:
1) when i click on the checkbox, it copies all the data in the same row as the checkbox to a seperate sheet (sheet2) and not leaving any spaces if checkboxes are not click in order.
Here comes the addition...
2) when i uncheck the checkboxes, the row that associated with the checkbox will be removed.
3) the checkboxes will all be uncheck with a commandbutton (reset switch)
4) variable number of checkboxes so that i dont have to link the subroutine to every checkbox with the move and remove subroutine. (because there will be unknow number of checkboxes and mostly 200+)
example (want to be able to replace all the 1, 2, 3...with an X or any way possible)
Private Sub CheckBox1_Click()
MoveCheckBoxData CheckBox1
End Sub
Private Sub CheckBox2_Click()
MoveCheckBoxData CheckBox2
End Sub .........................
I am currently creating a massive bilingual report using Excel based on data gathered from a customer visit, as most of the engineers are non-native English speakers, and most everywhere else in the world wants reports in English. Currently I am using this roundabout method:
I have a lookup Table sheet: .....
I'm currently studying japanese and I need to make a flashcard system so that I can memorise words a lot faster. An old friend told me that it is possible to make a flashcard system in excel (or maybe Access due to its database system - I cant remember which sorry). I'm looking at a basic system that first shows me the japanese - then i click it and it reveals the english, click the english and it then moves onto the next japanese word etc etc. Though, I have no idea where to start in building such a system, my excel knowledge is not great but it I'll give anything a try
View 2 Replies View RelatedIt looks like the above has been inserted into my current workbook (by mistake). How do I delete it all (lots of the options have the remove option disabled)
View 3 Replies View RelatedMy vlookups gives mostly #N/A in my full column, one or twice match with other sheet, so difficult to identify which one match in my lookup, i should watch carefully every time it will be matching or not, IS there any options when match it with formula then it appear highlight or color?
Or any options vlookup matching with original color and format?
See excel first sheet and second sheet : vlookup with identification.xlsx
How could I have a macro which translates formulas on file open since Excel doesn't do it itself for functinos from add-ins like YEARFRAC?
View 9 Replies View RelatedI am trying to figure out how to create a "User-Defined" enumerated type typically used in other languages (other than VBA).
for example in Java a typical enum type is declared like this:
enum Season { WINTER, SPRING, SUMMER, FALL }
This gives the benefit of writing "readable" code.
(back to vb)
If Season SUMMER then
call wear_a_sweater
end if