EXCELで質問があります。2～7行を1行にしたい

質問

EXCELで質問があります。弥生販売の請求書データを一度テキストに落とし、EXCELに書き出しました。A列からAP列まで（売上金額やら得意先名やらTEL番号やらです。）あり、行は300～400行あります。これを同じ得意先と同じ現場名で1物件とし、1物件ごとに1行したいので、お力をお貸し願いたいのです。

☆1物件のデータが、1～7行（7行までには確実に収まっています）あり、M列に請求書の行番号（1～7）がついています。例えば、

　　　　L　　　M　　 N　　　　　　　O　　　　　　　　P　　　　　　　　　Q
1 担当番号 1 他コード マルという得意先 ほし現場工事費　……

2 担当番号 2 他コード マルという得意先 ほし現場部品　　……

3 担当番号 1 他コード バツという得意先 月現場工事費　　……

4 担当番号 2 他コード バツという得意先 月現場部品　　　……

5 担当番号 3 他コード バツという得意先 月現場諸経費　　……

6 担当番号 1 他コード マルという得意先 太陽現場工事費　……

を、マルという得意先のほし現場を1行目、
バツという得意先の月現場を2行目、
マルという得意先（1行目と同じ得意先）の太陽現場3行目に
L　　　M　　　N　　O　　　　　　　　P　　　　　　　　　　　Q　　　　R　　　　　S　　T　　U　　　　　　V
1 担当番号 1 他コード マルという得意先 ほし現場工事費……担当番号 2 他コード マルという得意先 ほし現場部品(今2行になってますが、EXCELでは1行で。)……

2 担当番号 1 他コード バツという得意先 月現場工事費　…… 担当番号 2 他コード バツという得意先 月現場部品…… 担当番号 3 他コード バツという得意先 月現場諸経費(今2行になってますが、EXCELでは1行で。)……

3 担当番号 1 他コード マルという得意先 太陽現場　　　……

と、いう風に上記は、2.3行ですが実際のEXCELデータの行が2.3.4.5.6.7行あるものを現場ごとで1行（列は長くなってかまいません）にまとめたいのでM列の請求書行番号「1」が来たら動かさず「2」～「7」は行番号「1」の後ろに飛ばすという事が出来るものでしょうか。
どうにもマクロや関数の作り方がわかりません。
知識が乏しくて…。
しかし、 同じ得意先で、違う現場名（マルという得意先で、ほし現場やら鳥現場やら虫現場やら土現場やら同じ現場）や同じ現場名で違う内容や日付がいくつもあり、それは1行に出来ないものですから、得意先でくくると大変な事になってしまいます。

わかりづらい説明かとは思いますが、どうかご教授下さい。よろしくお願いいたします。

回答No.4

tom04 · ベストアンサー

No.3です！

補足に
項目行がない！ということがありましたので、データは１行目からあると
↓のコードに変更してマクロを実行してみてください。
表のレイアウトは前回アップした通りで、１行目からのデータだとしています。

Sub test2() 'この行から
Dim i As Long, j As Long, k As Long

j = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, "L").End(xlUp).Row '←1行目～Ｌ列最終行まで
For k = i + 1 To Cells(Rows.Count, "L").End(xlUp).Row
If Cells(i, "L") <> "" Then
If Cells(i, "N") = Cells(k, "N") Then
Range(Cells(k, "L"), Cells(k, j)).Cut Cells(i, Columns.Count).End(xlToLeft).Offset(, 1)
End If
End If
Next k
Next i
'空白行の削除
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") = "" Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　N列が一致するものを１行にまとめています。
こんなんではどうでしょうか？m（_ _）m

お礼

あっ、お礼の書く欄があったのですね。。。。。
初めて利用させてもらって、本当に助かりました。ありがとうございました。

補足

早くのご回答、本当にありがとうございます！！N列が一致するものを1行にまとめているとのヒントも頂き、とても役立ちました。実は、本来のEXCELがA列からデータがありますので、素人なのに「L」の所を「A」に変えさせて頂き、E列の伝票番号でくくると必ず物件ごとになる事が分かりましたので、「N」の所を「E」に変えさせて頂きました。それでもちゃんと物件ごとに1列にする事が出来ました。
ただ、素人が手を出してしまったので空白行が削除されませんでしたが、物件ごとに1行にする事が目標でしたので、すごく嬉しいです！

関数やマクロをいとも簡単にできるtom04様は、すごいと思いました。
何も分からないのに会社にやれと言われ、悩んでいたので色々助けて頂き、本当に本当に感謝しています。ありがとうございました。

回答No.5

kagakusuki

確認したいのですが、1行に並べるデータは、元データのL列～AP列にあるデータであり、A列～K列のデータやAQ列以降のデータは並べ替えた後の結果には表示しなくても宜しいのですね？
　それと、元データにおいて、得意先と現場が共通の行は全て、R列に入力されている伝票番号が同じ番号となっていて、且つ、伝票番号は必ず数字のみとなっていて、「#29222」とか「A2922」の様に数字以外の文字が含まれているようなものは無い、と考えて宜しいのですね？
　それでしたら、以下の様な方法は如何でしょうか。

　今仮に、「EXCELに書き出した請求書データ」がSheet1上にあり、Sheet1の1行目は、「日付」、「得意先」、「担当番号」、「売上金額」、「得意先名」、「TEL」等々の各項目の名称が入力されていて、実際のデータは2行目以下に入力されているものとします。
　又、並べ替えた結果をSheet2のA列～HI列に表示するものとします。

　まず、適当な未使用のシート（ここでは仮にSheet3とします）のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$R:$R,ROW())="","",INDEX(Sheet1!$R:$R,ROW())&"#"&COUNTIF(Sheet1!$R$1:INDEX(Sheet1!$R:$R,ROW()),INDEX(Sheet1!$R:$R,ROW())))

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に（元データの行数を上回るのに十分な行数となるまで）貼り付けて下さい。

　次に、Sheet2のG1セルに「伝票番号」等のなんらかの文字列を入力して下さい。
　次に、Sheet2のG2セルに次の関数を入力して下さい。

=IF(OR($G1="",COUNT(Sheet1!$R:$R)=0),"",IF(COUNTIF($G$1:$G1,MAX(Sheet1!$R:$R)),"",IF(ROWS($2:2)=1,MIN(Sheet1!$R:$R),SMALL(Sheet1!$R:$R,RANK($G1,Sheet1!$R:$R,1 )+COUNTIF(Sheet1!$R:$R,$G1)))))

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,$G2&"#"&INT((COLUMN()-COLUMN($A$1))/COLUMNS(Sheet1!$L:$AP))+1),INDEX(Sheet1!$L:$AP,MATCH($G2&"#"&INT((COLUMN()-COLUMN($A$1))/COLUMNS(Sheet1!$L:$AP))+1,Sheet3!$A:$A,0),MOD(COLUMN()-COLUMN($A$1),COLUMNS(Sheet1!$L:$AP))+1),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～F2の範囲とSheet2のH2～HI2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～HI2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、得意先と現場が共通なデータ毎に（実際は伝票番号が共通なデータ毎に）、1行ずつに並べ替えた表が、Sheet2に自動的に表示されます。

お礼

遅くなりまして申し訳ありませんでした。
回答ありがとうございます！！すごいです！
やってみたら、伝票番号は数字のみでしたので、シート2でデータが伝票番号ごとに1行になりました！

関数でも出来るんだと感心してしまいました。最初は貼り付けセルなどちょっとめんどくさいかなと思いましたが、多分慣れてくるとこっちの方がいいかもと思いました。
貴重なお時間を割いて考えて頂き、ありがとうございました。本当に助かりました。

回答No.3

tom04

こんばんは！
画像が小さくて詳細が判りませんので、勝手にこちらで解釈してやってみました。

とりあえずやり方だけ・・・
VBAになってしまいますが、一例です。

↓の画像のように上のような状態のSheet配置を下のSheetのようにしてみました。
1行目が項目行で、2行目以降にデータがあるとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long, j As Long, k As Long

j = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, "L").End(xlUp).Row
For k = i + 1 To Cells(Rows.Count, "L").End(xlUp).Row
If Cells(i, "L") <> "" Then
If Cells(i, "N") = Cells(k, "N") Then
Range(Cells(k, "L"), Cells(k, j)).Cut Cells(i, Columns.Count).End(xlToLeft).Offset(, 1)
If Cells(1, Columns.Count).End(xlToLeft).Offset(1, 1) <> "" Then
Range(Cells(1, "L"), Cells(1, "P")).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
End If
End If
End If
Next k
Next i

'空白行の削除
For i = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1
If Cells(i, "L") = "" Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　一旦マクロを実行すると元に戻せませんので、別Sheetでマクロを試してみてください。
※　行・列が一つでも違うと滅茶苦茶な表示になってしまいます。
実状のデータ配置に合わせて、コード内の行・列番号を調整する必要があります。m(_ _)m

補足

すごい！！ありがとうございます！私には、意味がさっぱりですが（笑）実際のEXCELの行・列は、質問例とは全く違いましたので、やはり変な表示になりました。しかしあと1歩という所まで前進出来ましたので、本当に感謝です！ありがとうございます。
本当はマンツーマンで教えて頂きたいですが、自分で少し勉強して色々応用できる様、試してみますっ。
画像が小さくて本当に申し訳ありませんでした。
あと、実際は項目行が無いのでその場合、0をどこに入れたらいいでしょうか？重ね重ね、頼ってしまいすみませんがよろしくお願いいたします。

回答No.2

kagakusuki

御質問の直接の回答ではなくアドバイスになりますが、

>実際のEXCELデータの行が2.3.4.5.6.7行あるものを現場ごとで1行（列は長くなってかまいません）にまとめたいのでM列の請求書行番号「1」が来たら動かさず「2」～「7」は行番号「1」の後ろに飛ばすという事が出来るものでしょうか。

という方法ですと、横方向が長過ぎて非常に見難くなると思います。
　普通は、同じ得意先と同じ現場名の物件が1塊になる様に並べた上で、得意先名と現場名に関しては一番最初に現れた行のみ表示する様にして、2回目以降に現れたものは空欄として、以下の様に表示させるのが一般的だと思います。

　　得意先　　　　　　　現場　　　　　種別　　 金額　　担当番号　　コード
マルという得意先　　ほし現場　　工事費　　\xxx　　　　1　　　　　他コード
　　　　　　　　　　　　　　　　　　　　　部品　　　 \xxx　　　　2　　　　　他コード
　　　　　　　　　　　　　 太陽現場　 工事費　　\xxx　　　　1　　　　　他コード
バツという得意先　　 月現場　　　工事費　　\xxx　　　　1　　　　　他コード
　　　　　　　　　　　　　　　　　　　　　部品　　　 \xxx　　　　2　　　　　他コード
　　　　　　　　　　　　　　　　　　　　　諸経費　　\xxx　　　　3　　　　　他コード

　この様な並べ方では駄目なのでしょうか？

　それから、追加情報を御教え頂きたいのですが、P列には「ほし現場工事費」の様に「『現場名』+『種別』」という形式で情報が入力されているとの事ですが、入力されている文字列の何処までが現場名で、どこからが種別なのかを判別する事が出来る様な基準はあるのでしょうか？
　例えば、現場名は「○○現場」等の様に、現場名の末尾には必ず「現場」という文字列で終わっているとか、
P列に入力されている文字列は「○○現場,■■費」等の様に、現場名と種別の間は必ず特定の文字か記号、或いは空白で区切られていて、且つ、その区切りを示す特定の文字や記号が現場名を表している部分の文字列に含まれている事はありえない、
末尾に記される種別は決まっていて、且つ、その種別を示す特定の文字列は、現場名を表している部分の文字列に含まれている事はありえない、
といった明確な条件があるのでしょうか？
　もし、その様な現場名と種別を区別する事が出来る様な基準が無い場合には、「同じ得意先と同じ現場名で一括り」にする事をコンピュータに自動的にやらせる事は不可能で、

　　得意先　　　　　　　現場・種別　　 　　　金額　　担当番号　　　コード
マルという得意先　　ほし現場工事費　　 \xxx　　　　　1　　　　　他コード
　　　　　　　　　　　　　ほし現場部品　　　 　\xxx　　　　　2　　　　　他コード
　　　　　　　　　　　　　 太陽現場工事費　　\xxx　　　　　1　　　　　他コード
バツという得意先　　 月現場工事費　　　 \xxx　　　　　1　　　　　他コード
　　　　　　　　　　　　　　月現場部品　　　 　\xxx　　　　　2　　　　　他コード
　　　　　　　　　　　　　　月現場諸経費　　　\xxx　　　　　3　　　　　他コード

の様に、同じ得意先でしか一括りには出来ませんので、必ず、現場名と種別を区別する事が出来る様な基準を御教え願います。

補足

おはようございます。アドバイス等、色々お考え頂き感謝です。
実はEXCELでの1物件を1行にするのは理由がございまして、その1行にした物件を会社で使っている[THE CARD]という[Acces]よりも昔のソフトに1物件づつ読み込む為なのです。物件を1行にしないと[THE CARD]は正しく読み込む事ができないようなのです。
また追加情報の件ですが、

＞現場名の末尾には必ず「現場」という文字列で終わっているとか
すみません、例には分かりやすい様「現場」と付けていますが、現場名に必ず決まった言葉ははいっておりません…。（例えばローズガーデンやら田中邸やらです。）

＞現場名と種別の間は必ず特定の文字か記号、或いは空白で区切られていて、且つ、その区切りを示す特定の文字や記号が現場名を表している部分の文字列に含まれている事はありえない
すみません、全ての現場名バラバラの違う文字になり、特定文字は一切無いのです…。

＞末尾に記される種別は決まっていて、且つ、その種別を示す特定の文字列は、現場名を表している部分の文字列に含まれている事はありえない
すみません、末尾はTEL番号なので数字と決まっていますが空白の時もあり、且つ現場名に数字が入っている事も多いです。（ツインタワー21やら貸工場32番やら）

と追加情報をお答えしたんですが、やはり1現場（1物件）に部品や工事費が含まれていても種別ごとではなく1現場（1物件）ごとに1行が上記に書いた通り、都合が良いものでなんか申し訳ありません。

あと追加補足と致しまして、1現場（物件）に対して伝票番号が付与されているので伝票番号は、どの現場（物件）ともカブる事は無いです。（R列に伝票番号有り。例えば伝票番号29222で次の物件は29223）
こんな大事な事を忘れておりました！本当にすみません！

回答No.1

aokii

つなぎたいセルが例えばA1,A2,A3,A4でしたら、A5セルに=A1&A2&A3&A4と入れてみてください。

補足

早速のご回答ありがとうございます。私の説明が悪かった様で申し訳ありません。
質問本文に書いた通り、行は300～400行あります。しかも1物件ごとにそれぞれ1行にしたいのですが、1物件の行がそれぞれ違います。その為、回答して頂いた事をする場合は手間がかかってしまいます。

EXCELのM列が請求書データの行数になっており当然物件によって行数が違うため（しかし最大7行です）M列に行番号（普通の数字です）がついています。（ほし現場は2行あり1・2、月現場は3行あり1・2・3、土現場は5行あり1・2・3・4・5とM列に数字があります。）
次の物件になるとまたM列の数字が「1」に必ず戻るのでその行番号の数字「1」になると、その場にとどまらせて、「2」～「7」だと1個上の「1」の後ろにデータが行く様なマクロや関数があれば、またもっと他の方法もあるよというのを教えて頂きたいなと思っております。1物件に2～7行ランダムにあるもので、どうしていいか分かりません。