Combine different lines to display beside each other

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
144
I am not sure how to ask this question. I want to display two different date fields of data beside each other. They have the same unique serial number/barcode and the dates are on separate lines. I want to display them on the same line beside each other. The first example shows how the data looks. The second shows how I want to display it. How do I move the dates side by side?

RA_NumBarCodeDate_EntDate_Warranty
RS07030128A-2366397/3/20184/5/2019
RT05240124A-2366395/24/2019
RS12200076A-32554212/20/20184/15/2019
RT05290139A-3255425/29/2019
RT04100249A-3348694/10/20195/21/2019
RT05280281A-3348695/28/2019
RS08280184A-3356638/28/20181/31/2019
RT02060109A-3356632/6/20193/28/2019
RT02190119A-3357572/19/20193/14/2019
RT03190145A-3357573/19/20194/22/2019
RS12280034A-33578012/28/20183/8/2019
RT03220089A-3357803/22/20194/30/2019
RT03010142A-3369523/1/20194/29/2019
RT06050101A-3369526/5/2019
RT01070052A-3587731/7/20195/28/2019
RT05280161A-3587735/28/2019
RT01180090A-3602151/18/20194/26/2019
RT05020219A-3602155/2/20195/22/2019
RS09060234A-3604519/6/20182/20/2019
RT03010097A-3604513/1/20194/18/2019
RT01300086A-3606661/30/20194/25/2019
RT05120051A-3606665/12/2019
RT01210130A-3622141/21/20194/23/2019
RT04240052A-3622144/24/20195/20/2019
RT05220057A-3622145/22/2019
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;"> <tbody> </tbody>


RA_NumBarCodeDate_EntDate_Warranty
RS07030128A-2366397/3/2018
RT05240124A-2366395/24/20194/5/2019
RS12200076A-32554212/20/2018
RT05290139A-3255425/29/20194/15/2019
RT04100249A-3348694/10/2019
RT05280281A-3348695/28/20195/21/2019
RS08280184A-3356638/28/2018
RT02060109A-3356632/6/20191/31/2019
RT02190119A-3357572/19/2019
RT03190145A-3357573/19/20193/14/2019
RS12280034A-33578012/28/2018
RT03220089A-3357803/22/20193/8/2019
RT03010142A-3369523/1/2019
RT06050101A-3369526/5/20194/29/2019
RT01070052A-3587731/7/2019
RT05280161A-3587735/28/20195/28/2019
RT01180090A-3602151/18/2019
RT05020219A-3602155/2/20194/26/2019
RS09060234A-3604519/6/2018
RT03010097A-3604513/1/20192/20/2019
RT01300086A-3606661/30/2019
RT05120051A-3606665/12/20194/25/2019
RT01210130A-3622141/21/2019
RT04240052A-3622144/24/20194/23/2019
RT05220057A-3622145/22/20195/20/2019
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3527;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;" span="2"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,784
Office Version
2013
Platform
Windows
I have no idea which columns and rows your data is in, but if it is in columns A:D and has headers in row 1 with data beginning in row 2, and you have no formulas in column D, the you could simply click on cell D2, right click, choose Delete from the pop up menu and then Shift Up from the options.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
Isn't this the Access forum?
This would be a tough query to figure out, I think. Thought I had something for a moment then I notice you have barcodes that have 4 dates when at first I thought the 2nd instance of barcode was always going to have no date. IMO, would take some code finagling (an inner and outer loop through a recordset). I can't think of a subquery approach because there is no consistent pattern, such as all 2nd row barcodes are null.

Another thought is to build a form that has duplicate barcode records and you copy from one field to the other (over-writing one) or do this right in the table field if it's a one time thing

P.S. - sorry if that first comment seemed snippy. Was not my intention
 
Last edited:

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
144
I am doing this all in Access. It has been a bear to figure out. The loop through a recordset was going to be my next attempt at this. Thanks for the feedback.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,784
Office Version
2013
Platform
Windows
I am doing this all in Access. It has been a bear to figure out. The loop through a recordset was going to be my next attempt at this. Thanks for the feedback.
Since this is an Excel forum, it would be helpful to know that you are working in a different application at the outset. Access has a different construct to its record set than Excel so data is manipulated by record rather than by rows and columns.
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
144
JLFGWhiz, I am sorry, I was under the impression that I was in the Access forum when I posted this. I was in the forum doing a search when I posted this.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
You were/are in an access forum. JLGWhiz, perhaps you picked this up from the Zero replies which includes posts from all of the forums on this board?

Tcurtis: I am struggling to work out the logic from your example. each barcode seems to appear in 2 rows on both the first and second data set. is that correct?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,784
Office Version
2013
Platform
Windows
You were/are in an access forum. JLGWhiz, perhaps you picked this up from the Zero replies which includes posts from all of the forums on this board?

Tcurtis: I am struggling to work out the logic from your example. each barcode seems to appear in 2 rows on both the first and second data set. is that correct?
Red face! It went completely by me. :oops: Of course, I blame it on senility.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
After reviewing my post I see that it could be construed that it was directed to the OP but is wasn't. Whenever I have my doubts I look at the thread cookies at the top to see where the post is. Can save some embarrassment because I did the same thing before (from the unanswered threads page).

As for the original problem, I have no more suggestions due to the uncertainty I pointed out.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
I'm waiting for answer to Stumac's question - or in general some more context. The semantics are not clear - why is the second case "right" and the first case "wrong". At first glance it seems you are just filling in a missing value but that's also seems like an insufficient explanation to cover all cases.

As a rule, with relational databases, you really can't define a problem in terms of "first row, second row" - it has to be defined in terms of the data. You can get something like "first row, second row" solutions by ordering records and dealing with them that way but it's clunky and usually not ideal - for instance, in this case, if you do use ordering, then you are relying on the "first row" always having the data and the "second row" always missing it, which is probably not a good way to approach data.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,841
Messages
5,446,824
Members
405,417
Latest member
egrospe17

This Week's Hot Topics

Top