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

NessPJ

NessPJ

Active Member
Joined
May 10, 2011
Messages
422
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Sequence[/TD]
[TD]Day[/TD]
[TD]Block[/TD]
[TD]Route[/TD]
[/TR]
[TR]
[TD]32441323[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]3243214[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1645[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]12342314[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2200[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]21343214[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1002[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]23141234[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1100[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]21342341[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1101[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]1234321[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]12343214[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1001[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]23141234[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1007[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]12342134[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3107[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]23141234[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3107[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]32141234[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]8977[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]12341234[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1245[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]23142134[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2201[/TD]
[TD]108[/TD]
[/TR]
</tbody>[/TABLE]

(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
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
238
MARZIOTULLIO
M
K
  • Solved
In column A locate partial text from column C then apply corresponding column D text to column B
Replies
3
Views
335
hagia_sofia
hagia_sofia
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
428
Peter_SSs
Peter_SSs
Ramballah
  • Solved
Finding the first certain value with conditions
2
Replies
18
Views
938
mumps
mumps
C
  • Solved
Lookup certain value in row and give back cell value in same column first row
Replies
7
Views
565
Fluff
Fluff

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

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