I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
I need a formula that will make every make every cell in column A unique. This formula needs to concatenate columns H, M, and N and display the result in column A. As you can see, you can get two instances of the same value (e.g., Dog:Brown:Small for A2, A9 and A10). For this reason, we then need to concatenate a counter number on the end that increases by 1 based on the total number of matching values ABOVE the cell in column A.
For example, I need a formula in cell A12 that concatenates H12:M12:N12 and then counts the number of "Dog:Brown:Small" instances above and then appends the largest incremented value +1. This would result in "Dog:Brown:Small:0004" in cell A12 and "Cat:Black:Large:0003" in cell A13. I will then drag this formula down column A and it will dynamically execute the formula against all column A cells above it.
I have the following example values in Column A and I want to concatenate each unique combination to a list.
A B C D
Example necessary output:
A vs. B A vs. C A vs. D B vs. C B vs. D C vs. D
Notice there are no duplicates (e.g., A vs. B / B vs. A). I have found many macros that will create all the duplicates but none that will create only unique.
I am trying to come up with a user defined function to accomplish several things at once.
First, I need to lookup a reference value in one column and determine the value from another column (on the same worksheet) in the same row. Then, concatenate each "return" value (that isn't blank).
I have the following code so far, but my formula results in a zero. If I remove the On Error Resume Next, the formula results in a #VALUE error.
Public Function ConcatUnique(Separator As String, Ref As Variant, LkupCol As Range, _ RetCol As Range) Dim lkup As Range Dim ret As Range Dim colDif As Long Dim mCollect As New Collection Dim i As Integer Dim b As Variant ' Determine the number of columns difference between ' the lookup column and the return value column. colDif = RetCol.Column - LkupCol.Column On Error Resume Next ' Determine which lookup values in the lookup range match the reference value. ' When the lookup value matches the reference value, set the return range object ' to the cell in the return range (column) in the same row as the lookup value. ' Note we use the difference between the lookup column and the return column to ' determine the location of the return range object. For Each lkup In LkupCol If lkup.Value = Ref.Value Then Set ret = Range(Cells(lkup.Row, lkup.Column _ + colDif)) ' Store the return value in the collection object. Ignore any blank return ' values. Note we use the range value converted to a string as the key ' value. If ret.Value <> "" Then mCollect.Add ret.Value, CStr(ret.Value) ' Loop through each cell in the lookup column range. Next lkup ' Write each item from the collection and the separator to the final result, ' writing each value and the separator after the previous value and separator. For i = 1 To mCollect.Count b = b & mCollect(i) & Separator Next i ConcatUnique = Left$(b, Len(b) - Len(Separator)) End Function
I need a mcaro to concatenate my project. I have 1 number in six separate cells that I need to be concatenated into a 3 digit number without duplicates. My data is in cells A4:C5 (six numbers) note that I have two duplicate numbers so the results should only yield (10) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly.
My data in cells A11:C12 (six numbers) are all different so the results should yield (20) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly. I also wanted to know will I be able to edit the macro to concatenate 5, 6, 7, 8 or even 9 numbers into 3 digit numbers without duplicates. If so, how can this been done? The results would be 5#'s:10 results; 6#'s:20 results; 7#'s:35 results; 8#'s:56 results;
I need to create a series which must look like in third column. This can be done using concatenate and & function but what i need that 01 & 02... must get autogenerarted and i have to use only first two column as source.
I have to create ranges of unique numbers that follows the below rules.
1) Have to start with 3 standard digits (ie 101 or 102 etc) 2) The total length of the is has to be 10 digits (ie 1010123456) 3) The second part (the last 7 numbers) must be unique! 4) Can create a lot of numbers (more that 5000 rows) 5) thats all with the unique numbers
Continue,
Somewhere in the sheet has to be a search function to find where a specific id is located, make it red and copy/cut in to another sheet.
I am attempting to create a unique list on column B of tab "1" from columns B of tabs "2" through "5" for which i have the following array formula thus far,
I have a big keyword list saved in Excel. Something like the following,
These are all keyword phrases;
car rent car hire cars for rent uk etc etc etc
All listed in Column A
All phrases in Column A, and in separate rows.(1 phrase per row.)about 2000 lines (Rows) in total.
What I want to know is there any way of selecting the whole list and exporting it (To save it as another list. A list of just unique keywords?
So, It would create a list like; car rent uk hire for etc etc etc.
So basically I want excell to look at all the words and export them to another list showing just unique keywords, 1 per line.
This is so I can see from a huge list what all the unique keywords are. Is there a way of doing this within Excel Now?? or has someone made a plugin ( Macro) to achieve this?
I've looked at the sort & filter options, but it doesn't appear to have this function?
What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com
The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?
I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).
Trying to do Vlookup with If but dont know how , I can do the concatenate function and then Vlookup as in Sheet 3 but that is too much of load to deliver in quick time specially i have pull data in dynamic and continuous update.
I want a report header to be similar to: REPORT AS OF 2/23/10. If I use the TODAY() function by itself, I get the date; 2/23/10. But when I concatenate it with the text "REPORT AS OF " & TODAY()" I get REPORT AS OF 40232". How do I preserve the date format when I concatenate it with text?
I currently have a list of companies. I want to create a unique list of these companies which I can use for some irrelevant purpose. What i did was I started recording a macro in which I first deleted everything in the spreadsheet. Afterwards I copied the whole company list from another spreadsheet and pasted it as values into the new spreadsheet. Afterwards I made an advanced filter; list range being the companies and copied it into a new location after making sure I had said yes to unique only.
I am creating an excel worksheet. Every time a user opens the worksheet (from a template), I want to get the next numeric sequence number as a reference, showing it in the sheet. The template is available to multiple users but the sequence number should be unique, ie if user Tom has sequence 1, Jerry will get 2, then Anne will get 3 and Tom's next one will be 4. I considered an ASCII text file with a number in, eg 1. Upon opening get OPEN text file, READ text file, Convert to numeric.. add 1.. use this. Convert to string.. WRITE text file. It seems clumsy and since I can only open with READ or WRITE (append no good, since I want only 1 value held), the READ allows multiple users to access simulataneously, and potentially get the same sequence.
Is it possinle to: LOCK file OPEN file for read READ file CLOSE file OPEN file for write WRITE file CLOSE file UNCLOCK file
There are likely to be several users accessing the template and the chance of them opening it at the same time is limited, but possible.
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T
he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is
VB: Dim iInput_Rows As Integer iInput_Rows = Worksheets("Workspace").Range("D3").Value Dim iOutput_Rows As Integer iOutput_Rows = Worksheets("Workspace").Range("D1").Value Dim iAnalog_Rows As Integer
[Code] ....
My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.
Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).
I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?
