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

NessPJ

NessPJ

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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
357
MARZIOTULLIO
M
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
377
Welshy1491
W
K
  • Solved
In column A locate partial text from column C then apply corresponding column D text to column B
Replies
3
Views
341
hagia_sofia
hagia_sofia
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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