Posts by singh.r

    Hi


    I need to match data in 2 sheets based upon matching 3 columns where 1 column is common in both sheet but remaining 2 may be same or nearest value.
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Sheet1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Sheet 2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Key1

    [/td]


    [td]

    Key2

    [/td]


    [td]

    Key3

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Key1

    [/td]


    [td]

    Key2

    [/td]


    [td]

    Key3

    [/td]


    [td]

    Value

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    01/04/2012

    [/td]


    [td]

    1000

    [/td]


    [td]

    KEF12

    [/td]


    [td][/td]


    [td]

    XYZ

    [/td]


    [td]

    12/06/2013

    [/td]


    [td]

    8600

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    12/05/2012

    [/td]


    [td]

    9000

    [/td]


    [td]

    KEF15

    [/td]


    [td][/td]


    [td]

    ABC

    [/td]


    [td]

    11/04/2012

    [/td]


    [td]

    998

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    XYZ

    [/td]


    [td]

    10/06/2013

    [/td]


    [td]

    8570

    [/td]


    [td]

    KEF89

    [/td]


    [td][/td]


    [td]

    MNP

    [/td]


    [td]

    13/10/2013

    [/td]


    [td]

    2850

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    MNP

    [/td]


    [td]

    01/10/2013

    [/td]


    [td]

    2800

    [/td]


    [td]

    KEF44

    [/td]


    [td][/td]


    [td]

    DEF

    [/td]


    [td]

    17/05/2013

    [/td]


    [td]

    9002

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    As in example above in i need to match key1/2/3 in sheet 1 & 2 & fetch the value column in sheet 2. But the problem is key1 is same in both sheet but key2 which is date may be same or 2-15 days more than sheet 1 & key3 which is value could be +/- 5% in sheet 2 & considering all this I need to find first match in value column from sheet 1 & get in sheet 2.


    Please suggest formula/VBA for this.

    Re: Find & Swap values in column based on user input


    I posted this to MrExcel also today and the link is http://www.mrexcel.com/forum/e…er-input.html#post4066827


    71 Views and still no reply, may be I have not explained the problem properly. I have repeating serial numbers in one column which changes when data changes in key row. It's like priority order of key data. Now due to some reason I need to change the priority. So what I want to do is just interchange the priority with some other key so 1 will become 3rd & 3rd become one like that.


    I got an Idea that may be I can create some temp variables & do find & replace. For example on click of command button it will popup a input box asking 1st number that you want to swap and I will enter 7 so it will create a variable temp0=7, then it will ask for second number & I will enter 19 so it will create another temp1= 19, then it will create on more temp2 may be. then It will find all 7 & replace it with temp2 then find all 19 & replace with temp0 & then all temp2 with temp1. I think something like this should work. But I dont have the sufficient VBA knowledge to create code my self.


    Requesting some expert help.


    Thanks

    Hi


    I have a column with repeated values as in table below, I need to swap values (not moving rows) based on user input.
    So some input box solution or I can tell user to define values in 2 cells somewhere & based on that the code shall find & interchange values in that column.
    Like in below table I want to swap all 1 with 3. Please note that I want to swap value only , other adjacent cells or rows are not changing.
    The table is long & has thousands of records.



    [TABLE="width: 300"]

    [tr]


    [td]

    Original

    [/td]


    [td]

    After Swap

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/TABLE]

    Re: Transpose rows into cloums with left column used as key


    Quote from jindon;663905

    I don't know if this can be done by formula.


    You are now asking completely different question....


    I am really sorry about the question. While posting I was only thinking about some formula so I just gave a sample data. I thought i can conditionally transpose columns in row with index-match or something, In fact I almost got it done but some error were coming so I posted in the forum.


    And I am really grateful for your time & kind support. The new code works perfectly for my data.

    Re: Transpose rows into cloums with left column used as key


    Thank u very much Jindon, the code works perfectly. Is it possible to achieve same result with some formula?
    Also my actual data contains 2 more columns, I had not mentioned earlier as I was thinking it could be done with formula so I'll modify formula as per my need.
    But I am not familiar with VBA so could you please modify your code for that.


    [TABLE="width: 837"]

    [tr]


    [td]

    Customer

    [/td]


    [td]

    Date

    [/td]


    [td]

    Amount

    [/td]


    [td]

    Inv id

    [/td]


    [td][/td]


    [td]

    Customer

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]12/12/2012[/TD]
    [TD="align: right"]200.00[/TD]

    [td]

    INR/00029

    [/td]


    [td][/td]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]12/12/2012[/TD]
    [TD="align: right"]15/10/2012[/TD]
    [TD="align: right"]01/01/2013[/TD]
    [TD="align: right"]01/06/2013[/TD]
    [TD="align: right"]02/04/2013[/TD]

    [/tr]


    [tr]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]15/10/2012[/TD]
    [TD="align: right"]600.00[/TD]

    [td]

    INR/00029

    [/td]


    [td][/td]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]200.00[/TD]
    [TD="align: right"]600.00[/TD]
    [TD="align: right"]900.00[/TD]
    [TD="align: right"]4000.00[/TD]
    [TD="align: right"]4500.00[/TD]

    [/tr]


    [tr]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]01/01/2013[/TD]
    [TD="align: right"]900.00[/TD]

    [td]

    INR/00029

    [/td]


    [td][/td]


    [td]

    AAKASH

    [/td]


    [td]

    INR/00029

    [/td]


    [td]

    INR/00029

    [/td]


    [td]

    INR/00029

    [/td]


    [td]

    INR/00040

    [/td]


    [td]

    INR/00040

    [/td]


    [/tr]


    [tr]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]01/06/2013[/TD]
    [TD="align: right"]4000.00[/TD]

    [td]

    INR/00040

    [/td]


    [td][/td]


    [td]

    AMAN MEDICOS

    [/td]


    [TD="align: right"]02/07/2013[/TD]
    [TD="align: right"]07/05/2013[/TD]
    [TD="align: right"]11/11/2012[/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AAKASH

    [/td]


    [TD="align: right"]02/04/2013[/TD]
    [TD="align: right"]4500.00[/TD]

    [td]

    INR/00040

    [/td]


    [td][/td]


    [td]

    AMAN MEDICOS

    [/td]


    [TD="align: right"]3000.00[/TD]
    [TD="align: right"]6000.00[/TD]
    [TD="align: right"]1500.00[/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AMAN MEDICOS

    [/td]


    [TD="align: right"]02/07/2013[/TD]
    [TD="align: right"]3000.00[/TD]

    [td]

    INR/00004

    [/td]


    [td][/td]


    [td]

    AMAN MEDICOS

    [/td]


    [td]

    INR/00004

    [/td]


    [td]

    INR/00004

    [/td]


    [td]

    INR/00024

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AMAN MEDICOS

    [/td]


    [TD="align: right"]07/05/2013[/TD]
    [TD="align: right"]6000.00[/TD]

    [td]

    INR/00004

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AMAN MEDICOS

    [/td]


    [TD="align: right"]11/11/2012[/TD]
    [TD="align: right"]1500.00[/TD]

    [td]

    INR/00024

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Transpose rows into cloums with left column used as key


    Quote from patel;663869

    without deleting rows ?


    Thanx for looking into it.
    I have to delete rows but that can be done easily with remove duplicate option, I am just looking for correct formula. I tried Index & Match but it's not working properly.
    I don't want a VBA solution so I have not deleted rows.

    Hi


    I have data in two columns like as below, I want all data in second column to be in rows as in next columns.



    [TABLE="width: 624"]

    [tr]


    [td]

    Customer

    [/td]


    [td]

    Inv id

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AAKASH MEDICAL STORE

    [/td]


    [td]

    INR/00029

    [/td]


    [td][/td]


    [td]

    INR/00029

    [/td]


    [td]

    INR/00040

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AAKASH MEDICAL STORE

    [/td]


    [td]

    INR/00040

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AMAN MEDICOS

    [/td]


    [td]

    INR/00004

    [/td]


    [td][/td]


    [td]

    INR/00004

    [/td]


    [td]

    INR/00024

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    AMAN MEDICOS

    [/td]


    [td]

    INR/00024

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    ASHU MEDICOS

    [/td]


    [td]

    INR/00122

    [/td]


    [td][/td]


    [td]

    INR/00122

    [/td]


    [td]

    INR/00123

    [/td]


    [td]

    INR/00006

    [/td]


    [/tr]


    [tr]


    [td]

    ASHU MEDICOS

    [/td]


    [td]

    INR/00123

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    ASHU MEDICOS

    [/td]


    [td]

    INR/00006

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    BALA MEDICOS

    [/td]


    [td]

    INR/00125

    [/td]


    [td][/td]


    [td]

    INR/00125

    [/td]


    [td]

    INR/00035

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    BALA MEDICOS

    [/td]


    [td]

    INR/00035

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Foolproof Protection of VBA codes


    Quote from cytop;630177

    No rules violation - it's asking to *break* passwords/protection that gets heat!


    To be honest, if code security is important to you, that's something that should have been addressed before starting development... not after.


    Rather than go to the bother of repeating things that've been said many times, I'll just suggest a link - there's some good reading there.


    Thanx, I checked the link, got little better understanding now. I don't know much about VBA, whatever codes I have in my excel files it's due to helpful people like you on this forum, & internet. I just copy paste & do little modification here & there.

    Re: Foolproof Protection of VBA codes


    Quote from royUK;630198

    There are some softwares available e.g. Donex but most are expensive & I'm not sure how good they are. See if you can open this


    I was not able to open that. Could you please tell how to do that?
    The software I found disables password option, but in your file it says "Project Unviewable" so it's better that way.


    Thanx

    Hiii


    I know there is nothing foolproof & an expert can find the way out.


    But is there any way that which can make it difficult for other to look into excel VBA codes. Earlier I used to think that if I put a very complicated password I am safe & recovery programs won't detect that.


    But recently I found one tool (a excel file) which if you open along with your password protected excel (VBA), it simply removes password option & you can access & view codes directly (It's not guessing & Breaking). So any novice can also see your codes just like that within seconds.


    If I am violating any rule by posting this please forgive me. I just want to know if there is any better security for my excel files (congaing a lot of VBA codes)


    Thanx

    Re: conditional Select & concatenat selected cells/data like sum if


    Hii Jindon


    I got one small problem in the file, actually the key column is not the 1st column, it's second column. If you see the attached file you will understand.
    In this example "RAI-M038" & "RAI-M039" should not join. Right now every time I am moving column B as 1st column & running code to avoid this problem.


    Please modify.


    Thank you

    Re: conditional Select & concatenat selected cells/data like sum if


    Quote from jindon;627232

    singh.r

    Try the attached and see if this is what you wanted.


    A big thanx to you Jindon, this is exactly what I wanted. How ever there is a little problem, the columns are not adjacent to each other in my original worksheet. I had thought that I'll just change the cell address/refrence in VBA & it will work but it didn't ( I am not much familiar with VBA except copy/paste & little modification here & there). I have attached the actual workbook. Please modify the code according to that. As you'll see in sample file I have two concatenated colums, one for code & one for name.
    Also I don't want the button cause I already have some codes assigned with shortcut keys. Your code also I want to run with shortcut keys when required.


    Thanx again for your valuble time & help.

    Re: conditional Select & concatenat selected cells/data like sum if


    Quote from PCI;627225

    Why group L00006-59-PS has not third column as RAI-A021 & RAI-A038??


    Thanx PCI for looking into, no i want third column as a result of formula/VBA. Jindon has given the code below & it works fine.