Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search for duplicate, than place both rows on one row

0

I am looking to make an option log tracking spreadsheet using the downloaded trades from the brokerage

There are 2 separate sets of data one for an opening transaction  and one for closing

Each one can be buy or sell

So for each buy to open there is a sell to close for that symbol

It can also be a sell to open and buy to close

The one thing they have in common is the symbol

There are 8 columns and one of them is the Symbol column where the duplicates would be located (Column C for referrence)

I want to search for a duplicate without referencing a specific cell because the duplicate may not always be in that cell

I also don't want to specify a cell value because it will be changing with every new symbol

excelmacros-vbaconsolidate-data
MS Office Version: 2003
posted by: status1 on Fri May 15, 2020 at 9:34 pm
Answer
Discuss

Discussion

Please publish the code you already have and let us know where it has a problem.
Variatus (rep: 4889) May 16, '20 at 12:19 am
That's the problem I don't really have a code
I have some bits and pieces but nothing to put it all together
I imagine it would need some kind of IF statement but I am not sure where to start

I found a way after I did a sort I can move the second row up to the first row but it's not consistent because it can place the opening after the closing data
That was using the resize property

I found alot of examples that show how to find duplicates or move duplicatates or consolidate but nothing that takes an entire row that contains the duplicate and moves it to the same row

I need some kind of logic to find the row that contains the duplicate and than move that row to the same row as the duplicate

I saw a video on Youtube from Teachexcel that shows how to move a row with duplicate to another sheet but that specified the cell value in the code so that is not good for me because I would need to keep changing that value in the code
but I thought maybe someone here might have a better way to do it
status1 May 16, '20 at 9:40 am
Add to Discussion

Answers

0

Hello, status1

I would use the functions offset and match in following way:

       OFFSET(B3,MATCH($C3,$C4:$C$32,0),0)

this formula must be in the same row where you want to move the duplicate.

Let's suposse you are in row 3. Write down the formula in cell G3, the formula looks for the duplicated value C3 (Symbol) in the whole range just below C3 (in this example until row 32) and it writes the corresponding value in column B for that symbol.

For the others columns (you have 8) just copy the formula.

I attached a file as an example. I hope I have understood waht you need.

Regards
Basilio

posted by: Basilio (rep: 105) on Sat May 16, 2020 at 3:45 pm
Discuss

Discussion

Hello,
Thanks for the example
That is pretty close
While it moves the duplicate row to one row if I change one of the duplicates it leaves n/a behind
Here is a small sample from the spreadsheet that perhaps helps to solve this problem

 05/06/2020 SOLD OPENING $2,615  -SPXW200515P2615  PUT (SPXW) NEW S & P 500 INDEX MAY 15 20 $2615 (100 SHS)  05/14/2020 BOUGHT CLOSING $2,615  -SPXW200515P2615  PUT (SPXW) NEW S & P 500 INDEX MAY 15 20 $2615 (100 SHS)
I used a bunch of countif functions and I was able to have just a number  1 in one cell in one column to identify the row with the duplicate but I am still looking for a way to place the closing row on the same row as the opening row
Hopefully the sample data may help
In this sample I have the symbol in column E
status1 May 16, '20 at 5:59 pm
Sorry, I dont understand exactly what is the problem.

So I attached another file, this time with a macro.

1. Select the cells you want to look for duplicates, in column C. The range can be discontinious.
2. Last file, the formula looked up duplicates in the bottom data set. This time you can select in the lower or upper data set.
3. When run the macro, it will write in the same row that was selected the data from the duplicated row
4. In case there is no duplicate it will pop up a message with the "symbol" has no founded duplicated
5. In this file, when you make a change in a row there wont be any action. So, you have to rerun the macro to update. There are ways to run automatically the macro when you made a change, i do not know if it is a need.

I hope, this works better for you.

Regards
Basilio
Basilio (rep: 105) May 16, '20 at 9:44 pm
Looks interesting
I have to go over the code to see if it makes sense to me and also substiute my data and see how it works
One concern I have is that it's not just a buy and sell that have to be matched
because I can have a sell to open and also a sell to close but maybe it's not necessary I have to run the code with my data to be sure but this looks like a step in the right direction
status1 May 16, '20 at 10:24 pm
I substituted my code and as I expected it just places the duplicate regardless if it's an opening or closing trade but I can get around that by sorting it so that all the open orders are on top
Another problem I see is that it leaves a gap between the rows where ther is no duplicate It would be nice if I could move the matched trades to a new sheet otherwise I could just remove the rows that do not have duplicates

One other issue I found is that if I select 2 non duplicates it throws an error
It would be better if I could select the entire list or just run the macro without having to look for the duplicates first
status1 May 16, '20 at 11:04 pm
status1

I have change the file in the answer. The gaps and the error are fixed. At same time, you do not have to select anymore. The new answer runs the macro over the entire list of symbols and the rows are placed in other sheet without gaps.

I did not make changes regarding your firts comment, I do not understand what you want with closing and opening trade. I though there is only one opening and one closing for each symbol; so, just matching the symbols you have opening-closing pairs

The code is pretty easy. I go over the entire symbols then check one by one if there is more than one in the column; if it is true then I lookup for the duplicated and write its data in the same row otherwise it means there is no duplidated.

Try it and see how it works. Regards
Basilio 
Basilio (rep: 105) May 18, '20 at 1:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login