For every similar column C add the first value from column B in column G

NessPJ

NessPJ

Active Member
Joined
May 10, 2011
Messages
418
Office Version
  1. 365
Hi guys,

I'm trying to figure out if i can have a formula that will put the first value from column B in column G for every similar value in column C.

I have a file with testdata uploaded here: https://www.dropbox.com/s/cehp2rmi5ao5tta/Testfile01.xlsx?dl=0

So basically the column Route (C) has several values "101". For every one of those values i would like Column G to contain the value "23" (which is the first value to come across in the table for Route 101).

Is this possible? :)
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi all,

I tried to Edit my post and rephrase my question but i was too late to edit. Please look at this post instead!

Actual question:

Hi guys,

I have a file with testdata uploaded here: https://www.dropbox.com/s/cehp2rmi5ao5tta/Testfile01.xlsx?dl=0

I am looking for a way to have a formula or routine that will count the actual order of 4-digit values in Column B, for every value in Column C.

So for example in my Test file Route 148 (Column C) has its first 4-digit Location (Column B) value starting on Stop value (Column D) 5.
I would like the result in Column G to count this as number 1.
The second 4-digit Location (Stop value 6) should be counted as number 2 etc.

Is this possible?
 
Last edited:
Upvote 0
Try

=IF(LEN(B1)<4,"",SUMPRODUCT((LEN($B$1:B1)>3)*($C$1:C1=C1)))

Code: 
[TABLE="width: 609"]
<colgroup><col width="87" span="7" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87, align: right"]1[/TD]
[TD="class: xl63, width: 87, align: right"]10[/TD]
[TD="class: xl63, width: 87, align: right"]148[/TD]
[TD="class: xl63, width: 87, align: right"]4[/TD]
[TD="class: xl64, width: 87, align: right"]10:05[/TD]
[TD="class: xl64, width: 87, align: right"]10:05[/TD]
[TD="class: xl63, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1818[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]11:02[/TD]
[TD="class: xl64, align: right"]11:02[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1128[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl64, align: right"]11:17[/TD]
[TD="class: xl64, align: right"]11:17[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1198[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl64, align: right"]11:44[/TD]
[TD="class: xl64, align: right"]11:44[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1222[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl64, align: right"]13:03[/TD]
[TD="class: xl64, align: right"]13:03[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1622[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl64, align: right"]13:28[/TD]
[TD="class: xl64, align: right"]13:28[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]8564[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl64, align: right"]14:02[/TD]
[TD="class: xl64, align: right"]14:02[/TD]
[TD="class: xl63, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1469[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl64, align: right"]14:46[/TD]
[TD="class: xl64, align: right"]14:46[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]109[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl64, align: right"]15:27[/TD]
[TD="class: xl64, align: right"]15:27[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]148[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl64, align: right"]16:55[/TD]
[TD="class: xl64, align: right"]16:55[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl64, align: right"]11:16[/TD]
[TD="class: xl64, align: right"]11:16[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1530[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl64, align: right"]13:17[/TD]
[TD="class: xl64, align: right"]13:17[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1801[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]13:50[/TD]
[TD="class: xl64, align: right"]13:50[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1487[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl64, align: right"]14:12[/TD]
[TD="class: xl64, align: right"]14:12[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]5869[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]14:33[/TD]
[TD="class: xl64, align: right"]14:33[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1089[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl64, align: right"]14:58[/TD]
[TD="class: xl64, align: right"]14:58[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]149[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl64, align: right"]17:29[/TD]
[TD="class: xl64, align: right"]17:29[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl64, align: right"]11:35[/TD]
[TD="class: xl64, align: right"]11:35[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1806[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl64, align: right"]13:05[/TD]
[TD="class: xl64, align: right"]13:05[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1201[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]13:37[/TD]
[TD="class: xl64, align: right"]13:37[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]3101[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl64, align: right"]14:02[/TD]
[TD="class: xl64, align: right"]14:02[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]3100[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]15:28[/TD]
[TD="class: xl64, align: right"]15:28[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl64, align: right"]16:44[/TD]
[TD="class: xl64, align: right"]16:44[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Can you please show some expected results, as I'm not sure what you're after.
 
Upvote 0
If gaz_chops has understood you correctly, here is a non array formula that does the same thing.
=IF(B2<1000,"",COUNTIFS($C$2:$C2,C2,$B$2:$B2,">=1000"))
@gaz_chops
FYI the video link in you signature no longer exists ;)
 
Upvote 0
@ Gaz_chops: This gives me the desired results, but the calculation seems a bit slow (when i insert this method inside a VBA routine).
@Fluff: Gaz_chops his table is showing the expected results i was after.

Would there be a more efficient method using VBA perhaps? :)


[Edit]
@ Fluff: Did not see your second post. I will try and see if this improves the calculation speed. :)
[Edit 2] This formula seems to be a lot faster!
 
Last edited:
Upvote 0
One more (similar) question.

If i have data like in the example table below.

Is there a way to use VBA and to delete 'Duplicate' block numbers within the same Route?
(The entire range of cells eg. not the entirerow, should be deleted and the remainder of the table can shift up).

The logic should always delete the 2nd value, rather then the first (which is normal...but just stating).

KeySequenceDayBlockRoute
32441323111000105
3243214111645105
12342314112200105
21343214111002105
23141234111100102
21342341111101102
1234321111000102
12343214111001112
23141234211007112
12342134113107112
23141234213107112
32141234118977112
12341234211245108
23142134112201108

<tbody>
</tbody>

(So in this example the second value 3107 should trigger the deletion and the entire range of cells A##:E## should be deleted).
 
Last edited:
Upvote 0
Simply select the dat Then on the Data tab select "Remove duplicates", check "My data has headers", & unselect all, then select "block" & OK
 
Upvote 0
Fluff said:
Simply select the dat Then on the Data tab select "Remove duplicates", check "My data has headers", & unselect all, then select "block" & OK
Click to expand...
@Fluff: Thanks for the reply. I know about the Remove duplicates functionality. But is there a way i can do this using VBA? :)
 
Upvote 0
Yup, just record a macro whilst you do it manually :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Solved
Finding the first certain value with conditions
2
Replies
18
Views
313
mumps
mumps
C
  • Solved
Lookup certain value in row and give back cell value in same column first row
Replies
7
Views
390
Fluff
Fluff
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
321
ashleighbrown
A
P
  • Question
Complete Summary Table with data from multiple sheets
Replies
6
Views
299
pioshelby1980com
P
D
  • Solved
Excel 365 VBA module Identify value in first column of Table
Replies
5
Views
305
Joe4
Joe4

Forum statistics

Threads
1,215,183
Messages
6,123,522
Members
449,103
Latest member
Michele317

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top