find duplicate item in same column then complete a function

[ccollins917]

I need a vba code for the following. If I have the below
Column W is a concatenate function.
1 Column V Column W
2 -51474.79
3 -51474.79
4 0.00 1401
5 0.00
6 0.00
7 0.00 1402
8 0.00
9 0.00
10 0.00 1403
11 -563619.13
12 -563619.13
13 0.00 1405
14 -604892.10
15 -604892.10
16 0.00 1408
17 40731.03
18 40731.03
19 0.00 1401
3182.94
0.00
0.00 1402
0.00
0.00
0.00 1403
444346.54
444346.54
0.00 1405
486471.66
486471.66
0.00 1408
1299.86
1299.86

For r=2 to lastRow
Look in column w (Cells(r,22) to match 1401 to cells in same column, cells(i,23) next item with ID 1401 to the next match, then add both to offset cells(I,-1). End result: Cells(4,lastRow-1)51474.79(cells(3,22))+40731.03(Cells(17,22))= -10743.76 (Cells(4,22))

 

[hiker95]

ccollins917,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

We can not tell where your raw data is located, sheet name, cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here

Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[ccollins917]

Using Excel 2010 with 64Bit Windows

I need a vba code for the following. If I have the below
Column W is a concatenate function.
1 Column V Column W
2 -51474.79
3 -51474.79
4 0.00 1401
5 0.00
6 0.00
7 0.00 1402
8 0.00
9 0.00
10 0.00 1403
11 -563619.13
12 -563619.13
13 0.00 1405
14 -604892.10
15 -604892.10
16 0.00 1408
17 40731.03
18 40731.03
19 0.00 1401
3182.94
0.00
0.00 1402
0.00
0.00
0.00 1403
444346.54
444346.54
0.00 1405
486471.66
486471.66
0.00 1408
1299.86
1299.86

For r=2 to lastRow
Look in column w (Cells(r,22) to match 1401 to cells in same column, cells(i,23) next item with ID 1401 to the next match, then add both to offset cells(I,-1). End result: Cells(4,lastRow-1)51474.79(cells(3,22))+40731.03(Cells(17,22))= -10743.76 (Cells(4,22))

 

[ccollins917]

Using Excel 2010 with 64Bit Windows

Sorry, this
reply went through without the changes

 

[ccollins917]

[

VBA help....I think I know just enough to be dangerous but not enough to know better. Thank you in advance for assistance.
Row 2016 Period 1 to contain a formula for: If Reference Column match, use 1st and 2nd together to add Period 1 sales and COS (Offset), divide using 3rd reference Gal Sold + an increase (index and match from another sheet that uses the Same reference number.
Look in Reference column (Cells(r,22) to match 1401 to cells in same column, cells(i,23) next 1401 to the next match, then add both to offset cells(I,-1).

For r=2 to lastRow
If Cells(r,4) match, then

​

Cells(r-1,3).value="=Cells(r-1,3)+Cells(next matched reference row,3)"​

End result: (-3400.85+3105.85)/1574.24=0.1874

Below is just a sample, I normally have at least 200 rows and more columns.

Acct Description	Year	Period 1	Reference
Sales	2015	-3400.85
Sales	2016		1401
COS	2015	3105.85
COS	2016		1401
Gal Sold	2015	1574.24
Gal Sold	2016		1401

 

[ccollins917]

I'm currently using a pc laptop with Excel 2010 running Windows 10, 64 bit.

 

[hiker95]

ccollins917,

Your replies #3, #4, and, #5, do not help.

To start off, and, so that we can get it right on the first try, can we see your actual raw data workbook/worksheet(s)?

You can post your workbook/worksheet(s) to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[ccollins917]

Hopefully this works
https://www.dropbox.com/s/y4584wukwlg7v0q/140 fuel test MrExcel.xlsx?dl=0

 

[hiker95]

ccollins917,

Thanks for the workbook.

I have examined your workbook with the three worksheets, and, I can not understand what you are trying to accomplish.

Below is a screenshot where worksheet 140, cell W3 shows an error, and, in cell Z3 is displayed the correct formula to return the correct concatenated value.

Change the formula in cell W3, from this =(Cells(3:3, 2)) & Cells(3:3, 5) to this =B3&E5

Excel 2007
	W	X	Y	Z
1	FuelID	FuelID
2
3	#NAME?			1405
4	1401	1401
5
140

Cell Formulas
Range	Formula
W3	=(Cells(3:3, 2)) & Cells(3:3, 5)
Z3	=B3&E5

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[ccollins917]

Apparently, I have a combination of copies that I uploaded. I have that formula in my -code. What I need is to reference 1401 and add offset(,-13) figure to the next 1401 reference offset(,-13) figure together. Use the 3rd 1401 reference offset(,-13). then loop through to populate the remaining periods. Am I thinking too big?