CODEWITHSUNDEEP
excel
PrathapB
PrathapB

Reputation: 382

Extract values from column based on other column in excel

I am new to excel. I have an excel sheet similar to the following table(Date,Id) 

Date        Id
02.06.2014  314
            315
            316
            317
            318
03.06.2014  326
            327
            328
            329
04.06.2014  347
            348
05.06.2014  356
            357
            358
            359

I need to extract start id and end id for each day and my resultant should be like the following table(Date,Start id,End id)

Date        Start id  End id
02.06.2014  314       318
03.06.2014  326       329
04.06.2014  347       348
05.06.2014  356       359

The sheet is too big to do it manually. Is there any way to automate the process in excel . If so Please help

Upvotes: 0

Views: 14020

Answers (2)

ihightower
ihightower

Reputation: 3253

My solution without any formulas will be something like this... but you need to set up the oroginal data like below...that is repeat Date labels.. (which is easier to do as a clean up exercise)... e.g. Type "=A2" in Cell A3.. then select the range A3 to A16 or END for example.. Press Ctrl+G.. Click Special... Choose Blanks... which will select all the blank cells... and do a Ctrl + V to Paste the formula... which will automatically fill in the dates for each row based on previous row...

enter image description here

Date    ID
02/06/2014  314
02/06/2014  315
02/06/2014  316
02/06/2014  317
02/06/2014  318
03/06/2014  326
03/06/2014  327
03/06/2014  328
03/06/2014  329
04/06/2014  347
04/06/2014  348
05/06/2014  356
05/06/2014  357
05/06/2014  358
05/06/2014  359

Then do a pivot table.. with Date in Rows.. and Min of ID and Max of ID in Values.. Example as below..

enter image description here

Please let me know if this helped you.. in the comments.

Upvotes: 1

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

This answer is pragmatic, trying to keep it fairly simple but allowing you to automate the process as you asked. It requires a little bit of manual action and only works if the values in the column id are unique.

Screenshot to guide the answer

The first step is to condense column A to remove the empty cells. This can be done with a so-called array-formula as explained on several places on the web, for example here. In the example sheet, I used

=INDEX($A$2:$A$16, SMALL(IF(ISBLANK($A$2:$A$16), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)))

for cell D2 and then confirmed this by pressing Ctrl+Shift+Enter to make it an array formula. The formula will be surrounded by curly brackets. Cell D2 should now contain the first date value. Now grab the lower right corner of D2 and pull it down to populate the other values. If you pull down far enough, you will have all values. After that, you will get #NUM!. If that is a problem, check out the earlier link on how to avoid that by using the IFERROR() function.

Next is the formula for E2:

=LOOKUP($D2,$A$2:$A$16,$B$2:$B$16)

Again, pull down the lower right corner to fill the other fields in E. Note that this formula only works if the values in column B are unique. If that is not the case, a more complex formula is needed.

Finally, to populate column F, use

=INDEX($B$2:$B$16,MATCH($E3,$B$2:$B$16)-1)

in cell F2 and pull down. Note that this formula does not work for the last value in column F. The easiest is to manually fill out that value or, if you do not like that, search the web for a formula to find the last value in column B.

Upvotes: 1

Related Questions