Record data on two lines

Sorry about repeating the post but I think I messed up the first one
yeaterday!

I am receiving a spreadsheet from an agency with record information on
two lines.
Example:
Record 1: A1, B1, C1, D1, A2, B2, C2
Line 3 blank
Record 2: A4, B4, C4, D4, A5, B5, C5

There are over 100 records like this. They claim they can't fix it...
It originates from a Crystal Report.

How can I change the data so each record is on one line:

Record 1: A1, B1, C1, D1, E1, F1, G1
Record 2: A2, B2, .......................G2

Thanks for any help!
Dan

0
tizhmslf (5)
11/13/2005 3:06:21 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
346 Views

Similar Articles

[PageSpeed] 25

A simple way is to 1) select all of the data fields.  2) Click on "Data
on the top tool bar.  3) then select "Sort".  Choose the colum
containing "record 1:" etc.  In the first "Sort By" box.  then clic
ok.  Then highlight only the cells containing "a1, b1" etc...  repea
steps 2) and 3)  then click "Options"  Select "Sort left to right." an
then "ok".  click "ok in the sort window and your data should b
arranged in the way you want it.  

you then should probablygo back into the "options" window and chang
the selection back to "Top to bottom"  click "ok" then click "Cancel".

Hopefully I'm clear and not to wordy.  Hope it helps

--
goobe
-----------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1983
View this thread: http://www.excelforum.com/showthread.php?threadid=48466

0
11/13/2005 3:34:10 PM
Thanks, Goober...
I'll try it tomorrow at the office.
Dan

Dan wrote:
> Sorry about repeating the post but I think I messed up the first one
> yeaterday!
>
> I am receiving a spreadsheet from an agency with record information on
> two lines.
> Example:
> Record 1: A1, B1, C1, D1, A2, B2, C2
> Line 3 blank
> Record 2: A4, B4, C4, D4, A5, B5, C5
>
> There are over 100 records like this. They claim they can't fix it...
> It originates from a Crystal Report.
>
> How can I change the data so each record is on one line:
>
> Record 1: A1, B1, C1, D1, E1, F1, G1
> Record 2: A2, B2, .......................G2
> 
> Thanks for any help!
> Dan

0
tizhmslf (5)
11/14/2005 2:08:42 PM
Reply:

Similar Artilces:

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

-1026 JET_errRecordTooBig, Record larger than maximum size
I administer a single Windows 2003 domain, Native Mode only W3k servers and use Exchange 2003. I am seeing this error in all DC Directory Service Event Logs. Event Type: Error Event Source: NTDS SDPROP Event Category: Internal Processing Event ID: 2008 Date: 28/07/2004 Time: 6:38:04 PM User: NT AUTHORITY\ANONYMOUS LOGON Computer: TLSAD1 Description: Internal error: The security descriptor propagation task encountered an error while processing the following object. The propagation of security descriptors may not be possible until the problem is corrected. Object: CN=internal 29176...

Showing data per month & year for comparison
Hi, I need to show data for a particular month for 2 years (July 2004 vs. July 2005) and YTD data for 2004 & 2005 on the same pivot table. This will allow comparison between 2 months and YTD sales on the PT. I have grouped the dates and put them along the top of the pivot table and can choose month and year, but the YTD figures always change to the month figures. How do I get the YTD figures to not change when I choose a month? Confusing...yes. -- gman Hi, Can you upload a watered down version of the spreadsheet, because it is difficult to work out what the problem is? I will t...

How do I host email for two separate organizations on 1 box?
Hi everyone, I am currently hosting my company's email on Exch 2000 and I want to host another company's email on the same box. How do I do this?? I have tried changing the recipient policy but to no avail. When I want to generate a new user in AD, all I get is an option to create a user for already hosted organzation. I've already set up a separate public folder tree, Information Store, etc. I'm obviously doing something wrong. Can anyone help me?? It would be very much appreciated Riyat The simplest way I know is to add these accounts to your current ADS and Exchange serv...

one or two emails will not go through form same user to same recei
Exchnage 2003 We have strange issue. some time we will not receive emails from client and most time we will. same email can be sent without any modification in subject or content and it will go through. what can cause this? Any suggestion will be helpful. thanks Chad You will need to enable message tracking and SMTP logging. Identify a time when the user said he sent you a message that you did not receive. Run message tracking to see if it's being delivered to the desination MB. If it doesn't show, check your SMTP logs to see if there are any SMTP transactions occuring with them d...

Validation Data & Goto question
Hi Everyone, I tried this on my own, but got a big headache and no result. I have an excel spread sheet with 9 columns and about 3000 rows of just text. One of the columns has vendor names (column F) and their products. So, for one vendor, there may be one or 100 products listed in rows until next vendor starts. On top of this column I have a "validation listbox" with the list of vendors. If I click & drop down the list, I can pick any one of the vendor names. Then I wanted to go to a row containing first occurrence of that vendor's name. Almost like one would be using V...

xy scatter line goes below zero
The line drawn in my xy scatter chart goes below zero of the y-axis, is there any way I can stop this? My first 3 points are all zero and then there is a very steep increase, the line that connects them assumes that there is a dip into negative values before the increase when this is not actually the case. Hi, Have you got the Smooth line option ticked? Cheers Andy jon7 wrote: > The line drawn in my xy scatter chart goes below zero of the y-axis, is there > any way I can stop this? My first 3 points are all zero and then there is a > very steep increase, the line that connect...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

select distinct id with other data
Hello everybody, I have a table with such data: id name 1 Mario 1 Mario Krsnic 1 MKrsnic 1 MarioK The same Id refers to different names of the same person. how can I select distinct ID with both columns? If I use select distinct id,name then I get the above data. I would like to have only: 1,Mario(or some other variante of the name) In the result should every id appear only once. Thanks for suggestions! Mario Mario Krsnic wrote: >I have a table with such data: >id name >1 Mario >1 Mario Krsnic >1 MKrsnic >1 MarioK ...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Default Public View has records but columns are blank
A custom entity has a default list view but the entries in the list do not show any values. There are a number of lines in the list with an icon only. If I try to delete one the message says select an entry before attempting operation. I cannot select the lijne either. Have created many views before and never seen this. Other views are ok? Hi Steve, Try publishing the entity and see if this resolves the problem. HTH, Niths "Steve" wrote: > A custom entity has a default list view but the entries in the list do not > show any values. There are a number of lines in the...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

CEditView No line wrap
How do I keep CEditView in my SDI application from wrapping the lines of text entered? i.e., I want the horizontal scrollbar. "Joe Estock" <jestock@NOSPAMnutextonline.com> wrote in message news:vrs98r4qk4so3f@corp.supernews.com... > How do I keep CEditView in my SDI application from wrapping the lines of > text entered? i.e., I want the horizontal scrollbar. Give it the (ES_AUTOHSCROLL|WS_HSCROLL) styles. In a CEditView derived class you can insure this by ORing the CREATESTRUCT.style member in an override of PreCreateWindow. -- Jeff Partch [VC++ MVP] ...

Small problem with a line chart
Hi I have a smal problem with a line chart. The chart shows progress of my teams league points over a season, the data is derived from a series of rows which I fill in after a game. In the data, I am using COUNTBLANK to have a blank cell until the row contains data EG =IF(COUNTBLANK(P53:Y53)>0,"",((P53+U53)*3)+Q53+V53) The problem is that the data series assumes unfilled rows (IE the future unplayed games) are zero, so there is a line that connects from the top of my line down to the zero on my X axis. I would just like the line to stop at the last value. Can anyone suggest a w...

Wish to sync Outlook on my two laptops, how do I do this?
I have just set up Outlook on another laptop which I will use in another residence. I would like to ensure that whatever work I do on one, will be in sync and kept on the other. Any suggestions or help would be greatly appreciated. (Assume that would also allow contacts to be shared between laptops). Thank you. www.slipstick.com for third party solutions "Rarefind2009" <Rarefind2009@discussions.microsoft.com> wrote in message news:62FDC6FB-6C35-42CF-B59C-A5EFCC18F2EB@microsoft.com... >I have just set up Outlook on another laptop which I will use in anothe...

Q: Charting with two variables on the Y-axis
Using Excel 2000: Hey all, I have two tables and want a line graph based on one row, but with the values of another row: Votes: ------ Region1: 10, 10, 15 Total: 50, 60, 100 Satisfaction Rates: ------------------- Region1: 60%, 80%, 45% Overall: 50%, 90%, 50% I want a line graph that compares the satisfaction rates of region 1 vs. overall (ie, it will show two lines, one for region1, one for overall). That's pretty straightforward. However, what I also want is, for Region1, to show the vote counts as values above (or below) the datapoints for the region 1 line. Can this be ...

2D line shape
Hi, If I draw a line and draw another line from the end of the first line, the whole becomes instantly a 2D shape and the two lines themselves cannot be extended individually. I am having trouble with that. I often type in a cable number in a line and that number normally shows right on top of the line. Now if I type a number with the two lines I described above, the number appears in the middle of the new born 2D shape. Is there a way to change lines separately from each other after they have become a 2D shape ? Or is there a way to avoid them becoming a 2D shape ? Thanks, Arnold The ...

Why is phrase "SPAM:LOW" in Subject line of all incoming e-mails?
The phrase "SPAM: LOW" appears in the "Subject" line of all incoming e-mail messages, including those that already have a "Subject" line inserted by the sender. The phrase "SPAM: LOW" is simply added by (I assume) Outlook 2007, which I use for all e-mail services. QUESTION: WHAT CAN I DO TO STOP THAT PHRASE FROM APPEARING ON ALL INCOMING E-MAIL MESSAGES? It was not added by outlook. Either your mail server is running antispam software that added it or you are using an antispam addin in outlook that is causing it. -- Diane Poremsky...

Need query to return absence of record
Hello all, hope everyone is doing well this fine morning! I need some help in constructing a query. I have three tables, designed as so: Personnel FullName(PK) Department Classes ClassName(PK) TrainingHistory *Junc table between Personnel and Classes* FullName(FK) ClassName(FK) DateTaken TrainingHistory records every instance of a class taken by all personnel. I need to be able to find people who HAVE NOT taken a given class. I need to be able to pick what class I search for each time I do the query. For example, I need to know who in the organization has NOT t...

How can I add a caption for each data point in a bar graph?
When you place the mouse over a data point a pop up box appears with the series name and value. How can I add additional information to that box using excel...if its possible? See the Hover Chart Labels add-in on Tushar Mehta's web site: http://tushar-mehta.com - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Lexy wrote: > When you place the mouse over a data point a pop up box appears with the > series name and value. How can I add additional information to that box using > exce...

Aggregate data types mismatch
Using reporting services 2008. Getting a really weird error when calculating a variance between a budget and actual amount in the report. Both the underlying fields are of data type 'money', but when performing the calc in the report writer, i am getting the following error: [rsAggregateOfMixedDataTypes] The Value expression for the textrun 'Variance_MTD_ActualvsBudget1.Paragraphs[0].TextRuns[0]' uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a si...

Problems retrieving data from XML file
Hi all, I have been having problems trying to retrieve data from my xml file. What I am trying to do is select a certain group node based on its groupid value. When I have that node I want to select all the menu nodes related to that group that have a value=1 along with its items that have a value=1. I have been trying an xpath query but with no luck. I have included one of the queries I have been trying. Any help would be greatly appreciated! strExpression = "/configuration/groups/group/menus/menu/menuname[../@value=1 and ../@groupid= " & GroupID & "]" ...

Need formula to check data in 2 columns to sum 3rd column
Need a formula to enter on Report Sheet under Month as indicated from tables below. The data base worksheet has info inserted daily and the report will be on another worksheet to calculate as data is updated. Any ideas?? (Need total paid for all rows paid to Name 1 with dates in month of Jan) Data Base Worksheet Report Sheet NAME DATE PAID NAME JAN FEB Name 1 01/01/04 $200.00 Name 1 ??Paid ??Paid Name 1 01/22/04 $200.00 Name 2 ??Paid ??Paid Name 2 01/15/04 $200.00 Name 1 02/04/04 $200.00 Hi try the following =SUMPRODUCT(--(A1:A100="Name 1"),--(YEAR(B1:B100)=2004),--(MONTH(B1:...

To clone a Network Adapter record (WinXP+)
Hi. I have an Ethernet network adapter on my computer and it is present in the network connection list as the "Local Area Connection". What I want is to "clone" this INetConnection (and all of it's configuration components) so I could use independent "network connections", with independent setting sets, but referring to same physical device. Unfortunately INetConnection::Duplicate (http:// msdn.microsoft.com/en-us/library/aa365096(v=VS.85).aspx) returns E_NOTIMPL for the connection I have (I believe it's not implemented for everything except RAS...